Excel VBAでいくつか質問があります。

質問

Function rr(v)
rr = WorksheetFunction.Average(Range(Cells(v.Row, (v.Column - 2)), Cells(v.Row, (v.Column + 2))))

End Function

選択されたセルの前後±２で平均をとるマクロなのですが、なぜかうまくいきません、どこが間違っていますでしょうか？

For i = 4 To 8・・・
・・・ActiveChart.SeriesCollection(1).Values = "=graph!R13C" & i & ":R1013C" & i
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="=graph!R12C" & i
・・・
Next
という感じで複数のセルより自動的にグラフを生成するマクロ作成したのですが、このgraphという部分を現在選択しているシートにするにはどうすれば良いのでしょうか？
それと生成するグラフのタイトルをName:="=graph!R12C" & i
としてもうまくこのセルから中身を取り出すことができないのですが、
どこを変更すれば良いのでしょうか？
何卒よろしくお願い致します。

回答No.3

okormazd · ベストアンサー

#1です。

Sub test()

Set sh1 = Workbooks("Book1.xls").Sheets(1)
'これがないとだめ。ブック名やシート名は適宜。

For i = 4 To 8
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(1).XValues = Range(Cells(13, 1), Cells(1013, 1))
'ActiveChart.SeriesCollection(1).Values = Range(Cells(13, i), Cells(1013, i))
'ActiveChart.SeriesCollection(1).Name = Cells(12, i)
'ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=Cells(12, i)

'Activeなのがグラフシートだから、グラフシートにはセルなんかないから上記のような指定はだめ。オブジェクトを指定してやる。下記。

ActiveChart.SeriesCollection(1).XValues = sh1.Range(sh1.Cells(13, 1), sh1.Cells(1013, 1))
ActiveChart.SeriesCollection(1).Values = sh1.Range(sh1.Cells(13, i), sh1.Cells(1013, i))
ActiveChart.SeriesCollection(1).Name = sh1.Cells(12, i)
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=sh1.Cells(12, i)
With ActiveChart

'.HasTitle = False
'これはだめ。あとでTitleを参照するから。下記。Titleは適宜。

.HasTitle = True
.ChartTitle.Text = "XXXX"

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "ｘ"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ｙ"
End With
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

Next
End Sub

こんなところだが、軸を決めるというのは、目盛のことかな。

With .ChartObjects(2).Chart
With .Axes(xlValue)
.MinimumScale =最小値
.MaximumScale = 最大値
.MinorUnit = 補助目盛間隔
.MajorUnit = 目盛間隔
.ScaleType = xlLinear
.HasMajorGridlines = False
.HasMinorGridlines = False
End With

With .Axes(xlCategory)
.TickLabelSpacing = ラベル間隔
.TickMarkSpacing = 目盛をつける間隔
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

End With

などのようなことか。

回答No.2

imogasi

最初の部分だけやってみたので参考まで挙げます
Function rr(v)
c = v.Column
If c <= 2 Then GoTo err1
Set v = v.Offset(0, -2)
Set v = v.Resize(1, v.Cells.Count + 4)
MsgBox v.Address
rr = WorksheetFunction.Average(v)
Exit Function
err1:
rr = "err"
End Function
ーーー
Sub test01()
Set v = Range("C1:E1") '<--ここを適当に変えてテストのこと
x = rr(v)
If x = "err" Then GoTo err2
MsgBox x
Exit Sub
err2:
MsgBox "列指定エラー"
End Sub
--------
例データ
A1:G1
1 2 3 4 8 1 2
結果
3
---
回答ではRangeの引渡しがうまく行かないのでは？

回答No.1

okormazd

1.
動作すると思いますが、vの指定が違っていませんか。
この関数を埋め込むセルを平均の対象になるように選択していませんか。下記の例だと、vの指定はこの関数を埋め込むセルの列より3列以上左側にないとだめですよ。

Function rr(v)
rr = WorksheetFunction.Average(Range(Cells(v.Row, v.Column - 2), Cells(v.Row, v.Column + 2)))
End Function

2.
どうしたいのかいまいちです。
意味のないことをしているように見えますが。
ActiveChart.SeriesCollection(1).Values = "=graph!R13C" & i & ":R1013C" & i
iが変わっても、SeriesCollection(1)ですよね。変えた意味がないような。

set sh1=workbooks("xxx.xls").sheets(1)
set sh2=workbooks("xxx.xls").sheets(2)
sh1.ChartObjects(1).Chart.SeriesCollection(1).Values = sh2.Range(sh2.Cells(13, i), sh2.Cells(101, i))
sh1.ChartObjects(1).Chart.SeriesCollection(1).Name = sh2.Cells(12, i)

というようなことなのかな。

お礼

ありがとうございます。

２つめの質問なのですが、

For i = 4 To 8
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Range(Cells(13, 1), Cells(1013, 1))
ActiveChart.SeriesCollection(1).Values = Range(Cells(13, i), Cells(1013, i))
ActiveChart.SeriesCollection(1).Name = Cells(12, i)
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=Cells(12, i)
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "ｘ"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ｙ"
End With
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
・・・
Next
End Sub

頂いた回答をもとに改良を加えてみたのですが、
ActiveChart.SeriesCollection(1).XValues あたりでエラーが出てきます。sh2.Range(sh2.Cells(13, i), sh2.Cells(101, i))
みたいに、シートを含むかたちで書いてもエラーが出てきます。どこをどうすれば良いのでしょうか？

それとグラフのｘ軸、ｙ軸をグラフ生成の時点で決定することはできないのでしょうか？

何卒よろしくお願い致します。