VBA

Vlookupより便利な関数 Index関数とMatch関数の組み合わせ

VlookUp関数はみなさんよく使われる関数かと思います。

セルの表示形式をそろえて参照や列が間違えてなければきちんと表示されますね。

ただし、VlookUp関数の弱点は参照するコードやリストが右側にあると機能してくれません。

下記のリストです。

1行目はVlookUp関数を使用してますがエラーですね。こういうリストを使用する際には

2行目のIndex関数+Match関数を使用しています。

二つの関数が入れ子にされて覚えづらいかもしれませんが覚えれば非常に便利です。

 

ざっくり簡単に説明すると下記のようになります。

Index関数=(参照範囲,行番号,列番号)の値を返す。

Match関数=(調べる値,調べるもしくはの範囲,一致条件)が何番目にあるかを(数値で)返す

※Match関数は1つの行もしくは1つの列の範囲しか選択できません。

 

Index関数+Match関数Index(参照範囲,Match(調べる値,調べる行範囲,一致条件(上記のリストでは完全一致)),列番号)

Match関数で行番号を調べています。

画像のリストでは調べる列が1列しかないので列番号は1となります。

 

調べる列が2列以上ある場合、=(Index関数+Match関数+Match関数)という式になります。

 

関数やVBAは見ているより手を動かしたほうが上達も早いです。

まずは手を動かして実践をして覚えていきましょう♪

 

ではでは♪

セルの先頭文字に0を入力する セルの表示形式を設定する VBA マクロ

初心者の方だと セルに「010」と入力しても「10」としか表示されず、あれ?なぜだ?となる方もいるかと思います。

 

マクロで処理するときにもはまってしまう方もいるかと思います。

まさに自分がそうでしたが笑

 

対策として2点かあります。

・セルの表示形式を 文字列 にする

 

・セルの先頭に ’(シングルクォーテーション) を入力する

 

私のオススメというか私はセルの表示形式を列ごと文字列にして先頭に0を表示させています。

リストや計算の過程でもしかしたら、 シングルクオーテーションにする必要がある可能性も少なからずあるとは思いますが。

私の(未熟ではありますが)経験上ではほとんどのリストで文字列形式でも問題ないです。

 

ちなみに表示形式を標準に戻す場合は下記の通りです。

その他の表示形式をしたい場合はセル上で 

Controlキー +1(セルの書式設定を表示)→表示形式タブ→ユーザー定義のリストを選択→種類タブ上を全選択→コピペ

すれば設定できます(‘ω’)ノ

ではでは♪

ファイルを開く 指定したファイルを開く ダイアログファイルを出してファイルを開く VBA マクロ

ファイルを指定してマクロ処理をしたい!ってよくあることかと思います。

私自身、ファイルを開いて処理をするってことが多々あります。

 

実はマクロでファイルを開くって結構簡単だったりします。

Application.GetOpenFilename メソッドを使用します♪

Application.Getfilenameメソッドは選択したファイルの文字列しか返しません。なので選択すれば開くというわけではありません。

引数の指定は下記です。引数を指定しなくてもいいとは思いますが。

FileFilter→開くファイル形式を指定します。

FilterIndex→ FileFilterで指定したファイルのインデックス番号を指定し規定値を指定します。

Title →ダイアログボックスの名前を指定します。

ButtonText →通常使わないです。

MultiSelect→Trueで複数ファイルを開けます。Falseで一つのファイルのみ開けます。

 

サンプルは下記です。エクセルファイルとCSVファイルのみ開けるように引数指定しています。

ではでは♪

オートフィルターの設定・解除をする マクロ VBA オートフィルターがあるか(設定されているかされていないか)調べるマクロ VBA

オートフィルターの設定のショートカットキーに引き続き、

マクロでオートフィルターを設定・解除する場合についてご紹介します♪

 

まずは設定する方法について。

ややこしいですがオートフィルターには、

Autofilterオブジェクト と Autofilterメソッド があります。

違いとざっくりな使い方は下記の通り

 

Autofilterオブジェクト →  Worksheetオブジェクトに属している

オートフィルターが設定されているかされていないかの判定ができます。

 

Autofilterメソッド→ Rangeオブジェクトに属している

オートフィルターの設定と解除ができる

上のようなリストのラベルにオートフィルターを設定する場合下記のマクロで設定できます。

ただし、Autofilterメソッドでは設定と解除しかできません。

なのでオートフィルターを設定したままにしていたい・解除したままにしていたい時、

マクロ全体の処理がうまくいかない可能性があります。

 

そんな時にAutofilterオブジェクトを使用します。

Autofilterオブジェクトでオートフィルターがあるかないかを判断できます。

 

オートフィルターを設定したままにしたい時は下記サンプルをご確認ください。


※オートフィルターを設定させたくない場合は

If Not Labe Is Nothing Then

とすればオートフィルターを解除させたままにできます。

 

サンプルファイルも添付しておきます♪

オートフィルターサンプル

ではでは(‘ω’)ノ

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

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

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

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

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

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

黄色なら6

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

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

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

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

ここからRGB番号を調べて

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

※サンプルです。

 

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

 

ではでは♪

SUBSTITUTE関数とREPLACE関数の違いは? エクセル関数 シート関数

どちらも置換という関数で似ていますね。

ワークシート関数でどのような違いがあるかご紹介します。ちなみにマクロを使っていてもReplace関数は結構使います。

私自身、この二つの関数を知ってはいたものの、違いがわかるまで何度も何度も検証をしましたw

みなさんに共有するためにまずどう違うのかと一言で表すと、

SUBSTITUTE → 替え玉

→指定した文字を置き換える文字にする

REPLACE → 代替え

文字列の中の何番目から何番目までの文字を置き換える文字にする

 

例えを挙げます

A1セルに ランニングはジョギングではない という文字がある場合

Replaceを使う場合

=REPLACE(A1,7,5,”ウオーキングではない”)

5行目から置換していることに注目

 

Substituteを使う場合

=SUBSTITUTE(A1,”ジョギング”,”ウオーキング”)

置換対象文字列を置換対象文字列にするということに注目

 

Replaceは何文字列以降を指定の文字列に置換

Substituteは対象の文字列を指定の文字列に置換

ってな感じになります。

まずはいじってみて確認してみるのが理解の第一歩かと思います。

 

サンプルを添付します。興味があればご確認ください♪

repace,substitute違い

アクティブシートにグラフを挿入する グラフの挿入 VBA マクロ

グラフの挿入はちょっとやっかいです。

メソッドやプロパティがやたら多いので覚えきれないと思います。

ですので、

オブジェクトやメソッド等を最低限必要なものだけ自分なりに簡潔にまとめてみましたので下記ご参照ください♪

ChartMmap

 

もし覚えたい場合は

Chartオブジェクト

ChartオブジェクトのChartWizardメソッド

 

ChartObjectオブジェクト

ChartObjectオブジェクトのAddメソッド

を覚えればグラフを作る土台はできるのではないでしょうか。

 

グラフを作成するにあたってサンプルコードを作成しました。

コードの冒頭部でグラフがあれば削除するという初期化を設定しています。

If ActiveSheet.ChartObjects.Count <> 0 Then
ActiveSheet.ChartObjects.Delete ‘初期化
End If

シートにグラフがあるか・ないかを判断する場合にも応用できますね♪

サンプルファイルを添付します。

グラフサンプル

 

ご参考にしていただければ♪

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

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

 

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

 

Top ・ Left プロパティ

です。

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

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

 

構文は簡単♪

オブジェクト.Top

オブジェクト.Left

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


結果はこうなります。

range(“G2”)のTopは18.75

range(“G2”)のLeftは331.5

とのことです♪

全てのシートを選択する VBA マクロ

たくさんのシートを選択したい場合もあるかもしれません。

 

実は簡単なコードで選択できます。

 

ただ、マクロを 使える方であればシートを全選択するということはほとんどないかと思います。

シートの名前を設定する。シートを削除する。シートをコピーする。という処理がとても多くなります。

 

ファイル名を指定してブックを保存する 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

 

ではでは♪