VBA

イミディエイトウィンドウの更なる活用方法 VBA マクロ

イミディエイトウィンドウを使用するときは Debug.Print を用いて変数の中身を確認したりすることが多いかと思います。

 

イミディエイトウィンドウの更なる便利な活用方法として下記の方法をご紹介します♪

 

①イミディエイトウィンドウで直接コード入力!

 

簡単なコードによる処理や些細な処理等を、Subプロシージャから書く必要はありません。

 

例えば、A1セル背景を赤にしたいというときにはイミディエイトでコードを直接入力すれば反映されます。

しかも入力補助もでてきます。楽ちんですね。

 

 

応用技として選択範囲に対しても可能です。

 

 

②オブジェクト変数の中身を確認!

ForEach構文でループさせた場合のオブジェクト型変数の中身を確認できます♪

例えば、ワークシートの名前を調べる場合下記のようにForEachで回します。

変数wsが今どのシートにいるかをイミディエイトでしらべることができます♪

とはいえ変数の取得や計算の結果等は ”?” が必須となるので気をつけてください。

?の行でEnterを押せば結果を取得できます。

結果は下記です。

 

なかなか重宝する機能なので是非ともイミディエイトを活用していきましょう♪

 

ではでは♪(*^-^*)

マクロの処理速度を調べる Time関数 ループの処理速度を比較する

マクロの処理速度って地味に気になりますよね。

簡単な処理なら一瞬ですが、分岐やループの長さ等により徐々に遅くなります。

処理時間を調べるにはTime関数を使用します。

 

ちなみに私は

For NextでA列の1行目から最後の行(1,048,576)w  まで行番を割り当てる処理を計測しました。

マクロを実行させたらフリーズしました!ショック!

と思いきや、8分3秒で処理できましたww

是非みなさんも1行目から最後の行まで処理計測をしないことをオススメしますw

 

サンプルは1行目から5000行目まで行番を割り当てる処理の処理時間を調べるマクロです。

最後の行までではないので安心して確認してみてくださいw

ちなみにこれは2秒で処理されましたw

セルの色を調べる RGBを調べる マクロ VBA

セルの色の設定は Colorプロパティ と ColorIndexプロパティ があります。

ColorIndexで1~56の色を指定して色を調べる場合、

簡単な方法としてイミディエイトウィンドウを使用して、ColorIndexプロパティを使用しましょう♪

?Activecell.Interior.colorindex で数値を取得しましょう♪

セルの背景色がなければ -4142 となるかと思います。

黄色なら6

その他の色はどんなものかは実際にセルに表示してみてもいいかもしれませんね♪

Colorプロパティを使用する場合

セルの書式設定→塗りつぶしタブ→その他の色→ユーザー設定→色の設定→ユーザー設定を開くと

下記のような項目が表示されます。

ここからRGB番号を調べて

Activecell.Interior.Color=RGB(102,255,51)

※サンプルです。

 

とすれば背景色を設定できます♪

 

ではでは♪

セルの座標を取得する VBA マクロ 図形の位置 チャートの位置 ボタン 修正

図形やグラフを挿入する際には座標の指定が必要だったりします。

 

そんな時に座標を調べたいときに使うのが、

 

Top ・ Left プロパティ

です。

Topプロパティはエクセル画面上部から指定位置までの距離

Leftプロパティはエクセル画面左端から指定位置までの距離

 

構文は簡単♪

オブジェクト.Top

オブジェクト.Left

下記サンプルではイミディエイトウィンドウに距離を表示します。


結果はこうなります。

range(“G2”)のTopは18.75

range(“G2”)のLeftは331.5

とのことです♪

ファイル名を指定してブックを保存する VBA マクロ

今回はブック名を指定して保存するマクロについてご紹介します♪

下記の記事では保存しないで閉じるマクロでしたが、今回は保存する名前を指定して保存するマクロです

変更を保存しないで閉じる VBA マクロ

 

Workbooks.SaveAsメソッド を使用します!

引数がやたら長ったらしいです。。

Filename 保存するファイルの名前を表す文字列を指定します。
FileFormat ファイルを保存するときのファイル形式を指定します。
Password ファイルを保護するためのパスワードを表す15 文字以内の文字列を指定します。
WriteResPassword ファイルの書き込みパスワードを表す文字列を指定します。
ReadOnlyRecommended 読み取り専用で開くことを推奨するメッセージを表示するには、True を指定します。
CreateBackup バックアップ ファイルを作成するには、True を指定します。
AccessMode ブックのアクセス モードを指定します。
ConflictResolution ブックを保存するときの競合の解決方法を指定します。
AddToMru 最近使用したファイルの一覧にブックを追加するにはTrueを指定。既定値はFalseです。
TextCodepage 使用しない。
TextVisualLayout 使用しない。
Local 通常は使用しない。
※csv出力時
日付がm/d/yyyyになってしまう場合に、yyyy/m/dにする場合にはTrueを指定

覚えておくのは

Filename・Fileformat だけで十分じゃないでしょうか。

 

サンプルコードは下記

テスト用という名前のブックが保存されます。

Fileformatの定数については下記

またまた長ったらしいですが、

・xlOpenXMLTemplateMacroEnabled マクロブック

・xlOpenXMLWorkbook エクセルブック

こちらだけ覚えておけば問題ないんじゃないかとw

名前 拡張子 説明
xlAddIn .xls Excel 97-2003 アドイン 18
xlAddIn8 .xls Excel 97-2003 アドイン 18
xlCSV .csv CSV 6
xlCurrentPlatformText .txt テキストファイル -4158
xlExcel8 .xls Excel 97-2003 ブック
(Excel2007以降)
56
xlHtml .htm HTML形式 44
xlOpenDocumentSpreadsheet .ods OpenDocument スプレッドシート 60
xlDIF .dif dif (Data Interchange format) 9
xlOpenXMLAddIn .xlam Excel アドイン 55
xlOpenXMLTemplate .xltx Excel テンプレート 53
xlOpenXMLTemplateMacroEnabled .xltm Excel マクロ有効テンプレート 51
xlOpenXMLWorkbook .xlsx Excel ブック 51
xlOpenXMLWorkbookMacroEnabled .xlsm Excel マクロ有効ブック 52
xlSYLK .slk SYLK (シンボリック リンク) 形式
プリンタの問題を取り除くために使用
(破損した要素を除外できることがある)
2
xlTemplate .xlt Excel 97-2003 テンプレート 17
xlTemplate8 .xlt Excel 97-2003 テンプレート 17
xlTextPrinter .prn PRNファイル
プリンタに渡すデータをファイル化したもの
(印刷時の画面の「ファイルへ出力」と同じ)
36
xlUnicodeText .txt Unicodeテキスト 42
xlWebArchive .mht Webページのアーカイブファイル 45
xlWorkbookDefault .xls or .xlsx
(環境に依存)
通常のエクセル形式 51
xlWorkbookNormal .xls Excel 97-2003 ブック -4143
xlXMLSpreadsheet .xml xml スプレッドシート 46

 

ではでは♪

セルに関数を入れる 可変セル範囲 SUM関数・Vlookup関数 VBAマクロ

Range.Formula を使用します

 

サンプルコード

C1とD1の結果は同じです。

formulaを使わなくても =”数式” だけで実は関数は入れられます。

 

ただし、行が可変で式の範囲が不明の場合はちょっと複雑になりますが下記のように

FormulaR1C1を使用します。

 

サンプル

Range.formuler1c1.sample

 

サンプルでは見やすいように便宜上、セルB2にSUM関数を挿入していますが、

Range(“B2”).FormulaR1C1 = “=Sum(R[-1]C[-1]:R[” & Lrow & “]C[-1])”

Range(“B”& lrow).FormulaR1C1 = “=Sum(R[-1]C[-1]:R[” & Lrow & “]C[-1])”

上記のように変更すればA列の最終行に答えが表示されます。

range.formulaR1C1は、R1C1の行と列の数値番号が重要になります♪

 

ではでは♪

セルの文字を左・右に寄せる 上側・下側に寄せる マクロ VBA

セルの文字の位置は何気に大事だったりします。

VBAマクロでは下記のように使用します。

Rangeオブジェクトの

「HorizontalAlignment」プロパティで水平位置(左右中央)を、

「VerticalAlignment」プロパティで垂直位置(上下)を設定します。

「HorizontalAlignment」プロパティについて設定する値は下記の通りです。

※デフォルトの値は「xlGeneral」

定数 水平位置
xlGeneral 標準
xlLeft 左詰め
xlCenter 中央揃え
xlRight 右詰め
xlFill 繰り返し
xlJustify 両端揃え
xlCenterAcrossSelection 選択範囲内で中央
xlDistributed 均等割り付け

 

サンプルコード

均等割り付け・選択範囲内で中央は何だか使わなそうなので省きましたw

xlFil は面白いですね。使わないでしょうがw

 

「VerticalAlignment」プロパティについて設定する値は下記の通りです。

サンプルコード

 

ではでは♪

定数を宣言する 定数を使用する VBA マクロ 

今回は定数の使用方法について投稿します。

 

宣言方法は下記

 

Const  定数の名前  as  データの型  =  “文字列”  or  数値

 

文字列の場合はダブルクオーテーションを忘れずに!

数値の場合はダブルクオーテーション不要です。

サンプルコードは下記

 

 

ではでは♪

罫線(破線の格子)を設定する エクセルVBA 破線 格子 マクロ

続いて、 破線にするマクロをご紹介します

 

 

点線と破線でどう違うんじゃい!!と思った方は下記の画像でご確認ください笑

 

点線                   破線

 

 

 

 

 

 

※画像が見づらかったらごめんちゃい。実際にマクロを使用して確認してみてください♪

 

とりあえず違いがわかりづらいです笑

 

破線の方が濃く見えるかなって感じです。

 

ではでは♪