VBA メモ

2021年5月6日 · 1 min read · VBA Excel  ·
Share on:

VBAに関するちょっとしたメモ

目次

A列は、B列の同じ行を参照するという処理をしたい場合

1Range("A1:A100").Formula = "=B1"
vb

と書けば、 セルA1は"=B1"、セルA2は"=B2"と処理してくれる。
逆に絶対参照したい場合は

1Range("A1:A100").Formula = "=$B$1"
vb

とすれば全部B1を参照してくれる。
これは入力規制などでセル範囲を指定する場合も同じで

1With Range("A1:A100").Validation
2    .Delete
3    .Add Type:=xlValidateList, Formula1:="=B1:B3"
4End With
vb

と指定してもちゃんと相対的に入力規制を設定してくれる
またこれらは【R1C1参照形式】を使用するとしているとちゃんと処理も変わる
渡すのはただの文字列だが、思ってた以上に賢く処理しているみたいです。

既に入力規制があるところにAddしようとすると「1004：アプリケーション定義またはオブジェクト定義のエラーです」がでます。
逆に入力規制がない状態でDeleteしても特になにも言ってこないので、とりあえず最初はDeleteしとけば問題ないと思います

1With Range("A1").Validation
2    .Delete
3    .Add '入力規制内容
4End With
vb

通常の設定では

1With Range("A1").Validation
2    .Delete
3    .Add Type:=xlValidateList, Formula1:="=B1:B3"
4End With
vb

は設定できるのですが、【R1C1参照形式】に設定している場合、参照できずにエラーを出してしまいます。

そこでApplication.ConvertFormulaメソッドを使って置換を行います。
さらに現在の設定をApplication.ReferenceStyleプロパティで取得できます。

ですので

1Application.ConvertFormula(参照内容, ←の参照の形式, Application.ReferenceStyle)
vb

とすればどちらでも対応できるマクロになります。

<例>

1With Range("A1").Validation
2    .Delete
3    .Add Type:=xlValidateList, _
4        Formula1:=Application.ConvertFormula( _
5                    "=B1:B3", _
6                    xlA1, _
7                    Application.ReferenceStyle)
8End With
vb

Sort.Orientationはソートを行か列かの設定を行うプロパティである。
この引数はXlSortOrientationを指定しているが、この列挙の変数名がややこしいということで使われていません。→Sortオブジェクトのドキュメントにも使われていない
そこで代わりにxlTopToBottom(行方向の並び替え)、xlLeftToRight(列方向の並び替え)を使います。この列挙はエクセルのConstantsにあります。

1Excel.Constants.xlTopToBottom
vb

恐らく後付けでこの定数を作成したんだとおもいます。(新しい列挙型作ってそれを引数にすればいいのに…)