Excel VBA で最近気づいた便利なこと
最近また VBA を使ったお仕事をいただきました。
前回は PowerPoint を Excel からいじる VBA でしたが、今回は Excel VBA です。
今回もいくつか気づきがあったので、まとめておこうと思います。
んなこと知っとるわー、って人もいらっしゃると思いますが
ご容赦を…。
誰かの一助になれば幸いです!
ファイルを開かないで中身を確認する
今回は、わたしが作っている VBA ファイル以外に
15個くらい別の月報ファイルがあって、
その15個に対して値を見に行ったり書き込みに行ったりする作業を自動化しました。
毎月発生する仕事らしく、手作業でやると時間がかかり、
ミスも多くなるとのことでした。
つい『手作業』って言ってしまうよね…。PC でやっているのに。
値を書き込むのは(多分)ファイルを開かなければできませんが、
値を見に行くのはファイルを開かずともできます。
…もちろん、条件はあります。
ファイル場所・ファイル名・シート名・セル番地が
あらかじめわかっていること、です。
月報ファイルに対してその処理を行います。
月報ファイルは、1年間は同じファイルを使い続けるので
ファイル場所・ファイル名・シート名は OK です。
セル番地は月によって異なりますが、規則性があるので特定できます。
そのような場合であれば、相手のファイルを開かなくても
値を見に行くことは可能です。
セルにこのように記入します。
='C:\Users\Default\[SAMPLE.xlsx]Sheet1'!A1この数式は、下のように分解できます。
='【 場所 】\[【 ファイル名 】]【 シート名 】'!【 セル番地 】- シングルクォーテーション → 『 ‘ 』
- 場所 → 『C:\Users\Default\』
- 半角すみカッコ → 『 [ 』
- ファイル名 → 『SAMPLE.xlsx』
- 半角すみカッコ → 『 ] 』
- シート名 → 『Sheet1』
- シングルクォーテーション → 『 ‘ 』
- エクスクラメーションマーク → 『 ! 』
- セル番地 → 『A1』
コレを直接セルに書き込むと、
そのファイル・シート・セル番地に入っている値が転記されます。
コレ、前からできてた!?
以前似たようなマクロ作ったときは
Excel マクロ4.0を使って作ったんだけど…。
ただ、コレを書いてそのままにしておくと、リンクと判断されるので、
ファイルの有無とかそういう関係で面倒になりそうです。
なので、コレは VBA で書いて、
書いた直後にセルを値貼り付け状態にして数式じゃなくしてしまいます。
Cells(1, 1) = "=" & Chr(39) & Cell(2,1) & "[" & Cells(3, 1) & "]" _
& Cells(4, 1) & Chr(39) & "!" & Cells(5, 1).Value
Cells(1, 1) = Cells(1, 1).ValueCell(1, 1) にマクロで数式を書き込むマクロにしました。
- B1: Cell(2, 1) → 場所『C:\Users\Default\』
- C1: Cell(3, 1) → ファイル名『SAMPLE.xlsx』
- D1: Cell(4, 1) → シート名『Sheet1』
- E1: Cell(5, 1) → セル番地『A1』
それぞれのセルにそれぞれの情報が入っています。
E1のセルには『A1』という文字列が入っています。
つなぎの記号の『=』、『[』、『]』、『!』はそのまま入力しましたが、
シングルクォーテーション( ’ )は『Chr(39)』にしました。
コレについては、後ほど別の項目で説明します。
こんな感じで、セルに入っている文字列を組み合わせて、
マクロでセルに書き込むと、そのファイル・シート・番地の値が表示されます。
その直後に Cell(1, 1)に Cell(1, 1).Value を入れることで
値貼り付けと同じ状態にします。
コレで数式(リンク)ではなく値に書き換わるので、
面倒な参照とはおさらばできます。
『 ’ 』『 ” 』は Chr 関数で入れると便利
VBA のコードを書く時、
ダブルクォーテーション・シングルクォーテーションは面倒ですよね…。
ダブルクォーテーションを表示させたい場合、
何個重ねて書かなきゃいけないかわからなくなる…。
ここ最近でようやく気づいたのは、
Chr 関数で表示させてしまえばとっても便利だということです。
- シングルクォーテーション『 ’ 』 → Chr(39)
- ダブルクォーテーション『 ” 』 → Chr(34)
なんてシンプルなんでしょう…!?
もっと早く気づきたかった!
ActiveX コントロールがずれる現象
Excel 上にマクロ発動のボタンを作る場合、
ほとんどいつもフォームコントロールを使います。
もう1つの選択肢が ActiveX コントロールですね。
わたしはコイツが好きでなくて…。
なんかプッシュしたときにちょっとずれたり、
フォーカスがあるときとないときの文字の濃さが違ったり、
とにかく挙動が気持ち悪い。
対するフォームコントロールはとても素直な子なので大好きです。
が、ActiveX コントロールのほうがいろいろカスタマイズできるんですよね…。
なんといってもボタンの色を変えられるのはとても魅力的です。
今回のファイルではボタンを10個以上作ってしまったので、
見やすさを考慮して、用途に応じて色を変える必要がありました。
なので、ActiveX コントロールを採用しました。
泣く泣く採用しました。
ただ、挙動の気持ち悪さは、Excel の表示倍率を100%にしていれば
けっこう防げるものなんですね!
苦手意識の原因の1つはわたしの設定だったことに気づきました。
ちょっとだけ申し訳ない気持ち。
ActiveX コントロール、毛嫌いしててごめんよ。
これからもそんなに好きにはなれないかもしれないけど。
終わりに
短いですが、Excel VBA で最近気づいたことです。
Excel 大好きを公言していますが、
いつまで経ってもすべてを知ることのできない愛しい人です。
だからこれからもずっと愛し続けると思います。
マクロ作るお仕事は楽しいですね!
お仕事いただける方はご連絡ください(営業)
