VBA

数式のみをコピペする マクロ VBA

久しぶりの投稿になります。

令和もよろしくお願いします!

 

エクセルの表で対象行の挿入・削除・編集などの処理をしたいとき、数式のみをいじりたい!

 

ということがあるのはマクロを書いてる以上よくあることかと思います。

 

エクセルの機能で数式のみコピーってありますが値までもペーストされてしまいますよね怒

 

エクセルの操作上で数式のみペーストするには、

Ctrl  + G → セル選択オプション → ”数式”

を選んでやっと数式を選択できます。めんどい。

そもそもコピペ機能でそういう仕様にしてくれよMicrosoftさん

 

Ctrl  + G → セル選択オプションこれらの処理をマクロで表現するには

SpecialCellsメソッドを使います

A11せるに数式が入っている下記の画像の例

 

A11を選択するコードは下記です。

Sub FormulaCell()
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Activate
End Sub

Rangeオブジェクトに格納して処理もできるのでSpecialCellsメソッド自体は便利です

 

ではでは♪

WorkSheet Changeイベントの活用方法 シートチェンジイベント シートイベントシリーズその2

WorkSheet Changeの活用方法として挙げられるのは、

特定のセル範囲に対しての処理です。

 

例えば、

このセル範囲の

・任意の文字列しか入れられないようにしたい

・日付や数値しか入れられないようにしたい

・桁数を制限したい

etc…

 

Changeイベントではこのような問題を解決できます。

 

そんなわけで今回も引き続き第二弾としてChangeイベント使用例として、

セルA1からA10 の範囲内で、入力したセルの文字列が ”気まま” なら赤く塗る

という処理をご紹介したいと思います♪

まずは準備をします

 

Changeイベントで最も重要なメソッドがあります。それは、

Intersect メソッド です。

特定の範囲に指定したセルが被っているか、被っていないかを調べることができます。

 

サンプルコードを下記にご紹介します。

 

ではでは ちゃんと処理するかを確認します。

まずは処理対象範囲内のセルA1に ”エクセル” と入力します。

何も起きてないので問題ないですね♪

 

では本命の ”気まま” ではどうでしょうか

きちんと赤くなってくれました♪

 

こんな感じで特定範囲のセルの処理をすることができました。

というわけでChangeイベントは何かしら大活躍します。

是非皆さんも
Changeイベントをご活用あれ♪

ではではヾ(@⌒ー⌒@)ノ

モジュールレベル変数が嫌いな件(-ω-) VBA マクロ モジュールレベル変数のメリット・デメリット

これまでたくさんマクロを書いてきたのでよくわかりますが、モジュールレベル変数はデメリットばかりで嫌いですw

 

デメリット

 

■汎用性が低い

サブルーチンがたくさんある場合、

グローバル変数を参照されまくります。

グローバル変数が参照されればされるほどエラーがでやすくなっちゃいますよ(>_<)

オブジェクト型を使用してたら、処理対象ではないシートやブックに処理が行われる可能性あり!

計算がうまく行われない可能性あり!

 

■保守性が低い

このサブルーチンのグローバル変数はこれ、あのサブルーチンのグローバル変数の動きはこう、、、などなど

変数の後追いが面倒!

また、変数の型が変わればまたコードの書き直し、、、

めんどくせぇw

 

などなどの理由で自分はグローバル変数が嫌いです。

 

デメリットばかり言っても仕方ないので、

あえてメリットも言います

 

メリット

 

■初級者がとっつきやすい

最大の要素がこれだと思います。

モジュールレベル変数を使う人は、 Functionプロシージャ や 引数付きSubプロシージャ が扱えないからでしょう。

例えばブックとブックで計算や転記処理をする場合、

モジュールレベル変数でブックとブックの処理を強引にがちゃがちゃ実行させているのかと思います。

 

■コードの少ないモジュールなら変数がスッキリ?

強引にあえて言えば何度も変数を宣言しなくていいのでコードがスッキリ?する。かもしれません、、、

構造体使えばよくね?って思いますがw

 

こんな感じでモジュールレベル変数をディスってしまいましたw

是非、モジュールレベル変数の使用は控えましょうw

 

ではでは♪

セル内の改行を削除する VBA マクロ vbLf

セル内の改行は vblfを使用すれば削除できます♪

 

改行を削除したいセルに対してReplace関数とvblfを組み合わせればきれいさっぱり改行がなくなります!

 こんな感じの改行を下記のようにします!

 

 

改行コードは vbcrlf とか vbnewline とかありますが、

 

セルの改行vblf !と覚えておけばいいと思います♪

 

サンプルコードは下記です

 

ではでは♪

シートの名前・番号が変更されてもマクロを実行させる方法 VBA マクロ

シートの名前が変わってしまうとマクロがちゃんと実行されない!

シートの位置(番号)が変わってしまうとマクロがちゃんと実行されない!

こんな悩みを解決し、更にコードもスッキリさせる方法があります。

シートのオブジェクト名を任意の名前に変更することです♪

実際にどんな風にするかは簡単です!例としてシートが5枚あるエクセルのVBEを開きます

オブジェクト名を付けたいシートをクリックします。今回はシート1を選択します。

Sheet1をクリック

そこで F4キーを押します。するとプロパティウインドウが出現します。

ここで見てわかる通りオブジェクト名を選択できます。

今回はオブジェクト名を Kimama とします。

オブジェクト名がKimamaとなり、プロジェクトエクスプローラーの表記もKimamaとなりました

これでオブジェクト名の設定ができました♪では実際にマクロを動かして挙動を確認します♪ マクロでシート1のセルA1に ”エクセルVBA” と入力します。

応用技として、シートの初期化にオブジェクト名を指定できます。

上のエクセルでいうとシート1は残し、シート2~5を削除するというときに、シート1の名前やシートの位置が変わっていても削除されないようにすることができます♪

それでは、シート1の名前を ”きまま” に変更し左から4番目に移動します。

きままシートを4番目に移しました

そしたらシートを削除するマクロを実行します。

見事にきままシートだけ残ってくれました♪

オブジェクト名をしたシート操作は、

CodeNameプロパティが重要になりますね!

引き続き快適なVBAライフを♪

ではでは!

任意のフォルダを開く フォルダパスを指定して開く VBA マクロ


Application.GetOpenFilenameメソッドでファイル選択させるマクロを書いたはいいけど、

マクロ実行するたびにカレントディレクトリが変わっちゃう!


再度、選択対象のファイルのフォルダを選択するのがまためんどい!。。。

ようするに選択ダイアログを立ち上げる時点で指定のフォルダにしたい!!っていうときには、

FileDialogオブジェクト

を使用しましょう♪

このオブジェクトの使用方法は下記です!

必要最低限のプロパティとメソッドも書いてます♪

これも便利なオブジェクトなので是非覚えていただければと思います!

ではでは♪

URLリンクからファイルをダウンロードする VBA マクロ

決まったURLからファイルをダウンロードするにはAPI関数を使用します。

使用するAPI関数は URLDownloadToFile関数です。

やたら長いので及び腰になってしまいますが、重要なポイントは引数の渡し方です。これがわかってしまえばサクッと利用できます。

今回は日本の祝日のCSVファイルをダウンロードする方法をご紹介します。

サンプルコードは下記です。

上記のように簡単にダウンロードできてしまいます。

是非、API関数を使ってみましょう♪

また、ダウンロードしたファイルを開いてその後どうかするという処理はFSOでできます。

API関数でダウンロード → FSO でファイルを開いて処理という鉄板な処理になります。ちなみに自分は祝日ファイルをダウンロードしてカレンダーに転記させる処理を自動化しています。

非常に強力なのでFSOも是非覚えていきましょう♪

ではでは♪

BeforeDoubleClickの活用方法! ワークシートイベント活用シリーズ1

BeforeDoubleClickの活用方法として挙げられるのは、

特定のセル範囲に対しての処理です。

例えば、

このセル範囲のどこかの セルを  ダブルクリックしたら

・背景色を赤色にしたい

・行を挿入したい

・ユーザーフォームを起動させたい

etc…

BeforeDoubleClick ではこのような問題を解決できます。

そんなわけで今回は BeforeDoubleClick の使用例として、

セルA1からA10 の範囲内で、ダブルクリックしたセルの背景色が赤くなるという処理をご紹介したいと思います♪

こんな感じでまずはイベントの準備をしましょう♪

BeforeDoubleClickで最も重要なメソッドがあります。それは、

Intersect メソッド です。

特定の範囲に指定したセルが被っているか、被っていないかを調べることができます。

サンプルコードを下記にご紹介します。

ではでは ちゃんと処理するかを確認します。

まずは処理対象外のA11セルをダブルクリックします。

処理対象外なのでカーソルがアクティブになりました。

次に肝となる対象範囲のA2セルをダブルクリックします。

きちんと処理されました♪♪

こんな感じで特定範囲のセルの処理をすることができました。

というわけでBeforeDoubleClick は何かしら大活躍します。

是非皆さんも
BeforeDoubleClick イベントをご活用あれ♪

ではではヾ(@⌒ー⌒@)ノ

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

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

 

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

 

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

 

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

 

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

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

 

 

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

 

 

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

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

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

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

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

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

結果は下記です。

 

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

 

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

セルにリスト(プルダウンリスト)を設定する Validationプロパティ xlValidateList VBA マクロ

Excelではセルの入力規則を使用してセルにプルダウンリストを作ることができますね

 

セルにプルダウンリストを作るには Range.ValidationプロパティでAddメソッドを使用し、

セルタイプ:xlValidateListを設定します。

サンプルコードは下記。

セルA1にプルダウンリストに”気ままにエクセルVBA”と3行入力します。


マクロを書いているときプロパティのヒントから

.valueと.validationの選択間違いをちょいちょいする ようじえでした笑♪

ではではヾ(@⌒ー⌒@)ノ