1. グラフの内容を、データ入力に応じて変化させたい
Excel でデータから、グラフを作成。グラフをワークシートに埋め込んだとする。
このグラフをデータの入力応じて変化するようにしたい。
このために、グラフの範囲設定に OFFSET 関数を入力してみたけれど、できなかった。
OFFSET の具体的な結果が設定され、値が固定されてしまう。 (+_+)
2. マクロで記録したものを改造
そこで、マクロで、グラフの対象範囲を変更する操作を記録してみた。
Sub Macro1()
ActiveSheet.ChartObjects("グラフ 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
End Sub
これを元にして、ワークシートに埋め込まれたグラフを更新する関数を作成。
' ワークシートに埋め込まれたグラフを更新する
Sub SetChartObjectRange(strWorksheet As String, strChart As String, strRange As String)
Worksheets(strWorksheet).ChartObjects(strChart).Activate
ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub
呼出すときは、グラフが埋め込まれたシート名、グラフ名、範囲を文字列を引数に指定する。
SetChartObjectRange "Sheet1", "グラフ 1", "A1:c10"
ところで、グラフをアクティブにしてないと上記はエラーになる。アクティブにしないで更新する方法はないのかなぁ？
3. Excel におけるオブジェクト
上記 ChartObject オブジェクト とは、
ワークシートにある埋め込みグラフを表します。ChartObject オブジェクトは、Chart オブジェクトのコンテナとして機能します。(ヘルプより)
これに対して Chart オブジェクト というのもあったが、これは、
ブック内のグラフを表します (ヘルプより)
つまり、Chart オブジェクトは埋め込みではなく、一つのシートで一面グラフのもの (グラフシート) を表わすようだ。
ちなみによく使う Worksheet オブジェクト は、
ワークシートを表します。(ヘルプより)
当り前か。 ^^;
Excel のオブジェクトモデルを見ていたら、名前がよく似た Sheets コレクション オブジェクト というのがあった。
指定されたブックまたは作業中のブックにあるすべてのシートのコレクションです。Sheets コレクションには、Chart オブジェクトまたは Worksheet オブジェクトを含めることができます。(ヘルプより)
Workbook オブジェクト は、
Excel ブックを表します。Workbook オブジェクトは Workbooks コレクションのメンバーです。Workbooks コレクションには、現在開かれているすべての Workbook オブジェクトが含まれています。(ヘルプより)
そして、最上位には Application オブジェクトがある。これをまとめて図にすると、
4. グラフシートの範囲を設定
Chart オブジェクトを更新するには、
' グラフシートを更新する
Sub SetChartRange(strChart As String, strWorksheet As String, strRange As String)
Charts(strChart).Activate
ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub
呼出すときは、グラフシート名, グラフの対象のシート名, 範囲を文字列で指定する。
SetChartRange "Graph1", "Sheet1", "A1:c10"
上記 Charts コレクション とは、
指定されたブックまたは作業中のブックにあるすべてのグラフ シートのコレクションです。各グラフ シートは、Chart オブジェクトによって表されます。ワークシートまたはダイアログ シートにある埋め込みグラフは含まれません。
Charts(“グラフチャート名”), Worksheets(“ワークシート名”) というように、コレクションに対して文字列でその要素を指定するというのがパターンのようだ。これは Access で Forms(“フォーム名”) と指定するのと同じ。
5. グラフの対象となるワークシートの最後の行番号を知りたい
「No.8 ワークシートの最終行、最終列を取得する」によると、
UsedRangeプロパティは指定されたワークシートで使われたセル範囲を返します
これを利用して、ワークシートで使われている範囲のアドレスを文字列で取得。それを先ほど定義した埋め込み用のグラフの範囲を設定するプロシージャに渡す。
SetChartObjectRange "Sheet1", "グラフ 1", Worksheets("Sheet1").UsedRange.Address
しかし、これだと離れた列を対象とした、以下のグラフを作成できない。
最後の行番号を取得できた方が、柔軟に対応できそう。Address プロパティで取得した文字列から行番号を抽出することにした。
6. 正規表現を利用
Office TANAKA - Excel VBA(正規表現によるマッチング) によると、
VBAから正規表現を使うには、VBScriptが便利です。ただし、正規表現をサポートしているVBScriptはVer5.0からですから、IE5.0がインストールされているパソコンでないと使えません。
また、正規表現における後方参照は、VBAで正規表現 - へたれプログラマな日々 を参考にした。
ワークシート名を渡されると、使われている最後の行番号を返す関数を定義する。
Function LastRowNum(strWorksheet As String)
Set re = CreateObject("VBScript.RegExp")
strCellAddress = Worksheets(strWorksheet).UsedRange.Address(RowAbsolute:=False, ColumnAbsolute:=False)
re.Pattern = "[A-Z]+[0-9]+:[A-Z]+([0-9]+)"
LastRowNum = re.Replace(strCellAddress, "$1")
End Function
これで例えば、上記のグラフの範囲を設定するなら、
rownum = LastRowNum("Sheet1")
SetChartObjectRange "Sheet1", "グラフ 1", "A1:A" & rownum & ",C1:C" & rownum
7. 全体
' ワークシートに埋め込まれたグラフの範囲を更新する
Sub SetChartObjectRange(strWorksheet As String, strChart As String, strRange As String)
Worksheets(strWorksheet).ChartObjects(strChart).Activate
ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub
' グラフシートの範囲を更新する
Sub SetChartRange(strChart As String, strWorksheet As String, strRange As String)
Charts(strChart).Activate
ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub
' ワークシートの使われている最後の行番号を返す
Function LastRowNum(strWorksheet As String)
Set re = CreateObject("VBScript.RegExp")
strCellAddress = Worksheets(strWorksheet).UsedRange.Address(RowAbsolute:=False, ColumnAbsolute:=False)
re.Pattern = "[A-Z]+[0-9]+:[A-Z]+([0-9]+)"
LastRowNum = re.Replace(strCellAddress, "$1")
End Function
Sub UpdateGraph()
rownum = LastRowNum("Sheet1")
SetChartObjectRange "Sheet1", "グラフ 1", "A1:A" & rownum & ",C1:C" & rownum
End Sub
0コメント:
コメントを投稿