[VBA] 正規表現オブジェクトの使い方

Windows Script Host(VBScrpt)が使える環境なら、VBやVBAからも正規表現を使う事ができます。

ExcelVBAから正規表現が使えると、いろんな事ができるようになってとっても便利です。

正規表現オブジェクトの参照設定

正規表現オブジェクトを使う前に、参照設定でライブラリを追加しときます。

正規表現オブジェクト参照設定VisualBasicEditorのツール(T) - 参照設定(R)を開いて、「Microsoft VBScript Regular Expressions 5.5」を探し出して、チェックしてください。5.5のところは違うかもしれませんが、タブン、大丈夫です。

正規表現オブジェクト群

正規表現オブジェクトの関連オブジェクト/コレクションは以下の4つです。

・RegExp (メインのオブジェクト)
・MatchCollection (マッチしたデータを保持する)
・Match (マッチしたデータ本体)
・SubMatches (マッチしたデータの中から「抜き出し」を指定したデータ

ちょっと複雑そうですが、基本的に他のオブジェクトを使う時と同じです。

正規表現の基本

サブマッチ(SubMatches)は抜き出しを指定しなければ、必要ありませんので、使うのは3つ

正規表現オブジェクトを生成して、マッチパターンを作成します。HTMLのタグにマッチするパターンです。

Set MyRegExp = New VBScript_RegExp_55.RegExp
MyRegExp.Pattern = "<.*>"

で、実行します。実行結果を MyMatches コレクションに格納して、For Each で順番に回してやればOKです。

Set MyMatches = MyRegExp.Execute("テスト<HTML>TESTテスト")
For Each MyMatch In MyMatches
 Debug.Print MyMatch.Value
Next

Excecuteしただけでは、マッチした部分を取得できないので、面倒ですが上のようにしないといけません。

Debug.Print MyRegExp.Execute("テスト<HTML>TESTテスト")

ってのはダメです。

サブマッチ(SubMatches)の使い方

サブマッチは、マッチした部分の中から、さらに指定した部分を抜き出す為に使います。

上の例で言うと、括弧つきの <HTML> 全体じゃなくて、HTML っていう文字だけを抜き出したい、とかいう場合に使います。

いわゆる、タグ付正規表現です。

パターンを作る時に、括弧 ( ) で囲んだところが抜き出されます。

MyRegExp.Pattern = "<([a-z0-9]+).*?>"

サンプルソースコード

ローカルにあるHTMLファイルを読み込んで、HTMLタグ全体とそのタグ名(要素名)だけを抜き出すサンプルです。

実行結果(イミディエイトウィンドウ)

| | コメント (0) | トラックバック (0)

[Access] ADOXテンプレート

Accessでテーブルの一覧を取得したり、新しいクエリを作ったりするにはADOX(ActiveX Data Objects Extensions for Data Definition Language and Security)が必要です。

ADOXなら、新しいデータベースを作成する事も可能になります。

ADOXの参照設定

ADOXを使う前に、参照設定でライブラリを追加しときます。

ADOX参照設定VisualBasicEditorのツール(T) - 参照設定(R)を開いて、「Microsoft ADO Ext 2.5 DLL And Security」を探し出して、チェックしてください。タブン、真ん中あたりにあります。2.5とかの数字は、Officeのバージョンで違ってますので気にしない。

サンプルソースコード

Accessのテーブル一覧(システムが使ってるのは除いて)を表示するサンプルです。

実行結果(イミディエイトウィンドウ)

| | コメント (0) | トラックバック (0)

[Access] ADOテンプレート(SQL実行)

AccessのVBAで、ADOを使ってSQLを実行する方法です。

テーブルの全データを削除する時とか、SQLでDELETE文作ってする方が、全然早く消せます。

サンプルソースコード

「T_作業テーブル」というテーブルの全てのレコードを削除するサンプルです。

| | コメント (0) | トラックバック (0)

[Access] ADOテンプレート

Accessでテーブルやクエリを直接扱う時は、ADO(ActiveX Data Object)を使う事になってます。昔はDAOだったんですが、Access 2000くらい?からADOが標準になったようです。

カーソルタイプ

テーブルやクエリを開く(Openメソッドの実行)時に、カーソルタイプとロック方法の指定があります。

カーソルタイプですが、

adOpenForwardOnly (前方専用)
adOpenStatic (静的)
adOpenKeyset (キーセット)
adOpenDynamic (動的)

の4つ。下に行くほど遅くなるはずです。スタンドアロン(パソコン1台だけで使う時)なら、下の2つ(キーセットと動的)はあんま意味ないんで、普通は adOpenStatic で。最初から順番に1回だけテーブルとかを読んでいくだけなら、adOpenForwardOnly。でも、adOpenForwardOnly の場合、.RecordCount でレコードの数が取れません。

ロックタイプは、adLockOptimistic にしとけばいいと思います。更新しないなら、adLockReadOnly。

サンプルソースコード

ADO関数の使い方テンプレート。

| | コメント (0) | トラックバック (0)

[VBA] 動的配列 テンプレート

動的配列は、使用する配列の個数(=次元の添字)が分からない時に使うと便利です。使う時に、使う分だけ宣言しなおします。

Dim、ReDim

動的配列の宣言は、普通の変数宣言で空の括弧()をつけるだけです。

使う時になったら、ReDimで使用する配列の個数を指定します。Preserve を付けるとすでに格納されている値は保持したまま個数を増減させれます。

Dim sData() As String

ReDim Preserve sData(10)

サンプルソースコード

動的配列の使い方テンプレート。
今月の今日までの日付を整形して出力します。

実行結果(イミディエイトウィンドウ)

| | コメント (0) | トラックバック (0)

[エクセル] 「値の貼り付け」をショートカットキーで実行する

エクセルでショートカットキーの割り当てを変更する方法を使えば、「値の貼り付け」にショートカットキーを設定できそうです。

できそう・・・ですが、標準のままでは設定できません。「値の貼り付け」コマンドが、メニューバーのどこにもないからです。

メニューバーに「値の貼り付け」を追加

  1. ツールバーのどこでもいいので右クリック、「ユーザー設定(C)」をクリックして設定画面を開きます。
  2. コマンド(C)タブをアクティブにして、分類(G)から「編集」を探します。
  3. 右側のコマンド(D)から「値の貼り付け」を探します。
  4. そのままの状態で、メニューバーの「編集(E)」をクリックして開きます。固定されて閉じません。
  5. さっき探した「値の貼り付け」をドラッグアンドドロップして、「形式を選択して貼り付け(S)」の下にでも移動します。

「値の貼り付け」のD&D

  1. 編集(E)メニューに追加した「値の貼り付け(P)」を右クリックして、名前を変更。「値の貼り付け(&V)」にします。

「値の貼り付け」の名前変更

サンプルソースコード

あとは同じです。[Ctrl] + [B] で、「値の貼り付け」をできるようにします。

画面のちらつきを抑える

以下は、気にしなければ別にかまいません。

SendKeys でやってる以上、画面がちらつく(特に高負荷状態の時)のは抑えようがありません。

ただ、「値の貼り付け」コマンドの場合、ある条件が重なるとほぼ100%画面にゴミが一瞬、残ります。

ある条件ってのは、「値の貼り付け」が使えない時。クリップボードにHTMLが入ってたり、単純なテキストしか入ってない場合、「値の貼り付け」は使えません。使えないのに実行しようとするから、ゴミが出ます。

CommandBarControl

「値の貼り付け」が使えない時には、コマンドを実行しないようにすれば、回避できます。

「値の貼り付け」が使えるかどうか調べるには、CommandBarControl で 「値の貼り付け」コマンドの .Enabled プロパティを見れば分かります。使えない時は、SendKey する前に抜ければOKです。

サンプルソースコード

[Ctrl] + [B] で、「値の貼り付け」をできるようにします。ちらつき回避版。

| | コメント (2) | トラックバック (2)

[エクセル] ショートカットキーの割り当てを変更する

エクセルのキーボードショートカットの変更(ショートカットキーのユーザー設定)、需要はあると思うんですが、Microsoftは搭載する気はないんでしょうか?

ちょっと大変ですが、ショットカットキーをユーザーが自由に設定する方法が2つあります。

「値の貼り付け」をショートカットキーで実行する方法も参考にしてください。

[Alt] + ? 限定

[Alt] + [P] など、[Alt] キー のみしか使えませんが、割と簡単に設定できる方法です。

【裏技】あたいの磔!

1.ツール-ユーザー設定-コマンドタブから分類:編集 コマンド:値の貼りつけをツールバーにドラッグアンドドロップ

2.1のコマンドタブの[選択したボタンの編集]から[テキストのみを表示]をクリック

ツールバーにあるボタンであれば、アイコン表示をやめて、テキスト表示にする事で ( ) 内の文字がショートカットキーになります。

新規作成なら、[Alt] + [N]。上書き保存なら、[Alt] + [S]

Application.OnKey

柔軟な設定がしたい場合は、VBA を使って、Application.OnKey メソッドを実行します。

Application.OnKey(Key, Procedure)

Key:キーの組み合わせ
Procedure:実行する関数

ただし、Application.OnKey メソッドは、指定されたキーを押された時に、指定されたプロシージャー(関数)を実行するコマンドです。あらかじめ、希望のショートカットキーと同じ動作をする関数を作っておく必要があります。

Application.OnKey メソッドは、個人用マクロブック personal.xls の中に Auto_Open 関数を作成し、その中においてください。

もっと詳しい説明は、personal.xls 強化講座 その2 を参考に。

サンプルソースコード

[INSERT]キーで、行の挿入ができるようにします。VBAコードに貼りつけた後、一度エクセルを再起動してください。

Application.SendKeys

上のやり方でとりあえずOKです。ただし、この方法だと、[Ctrl] + [Z] (「編集(E)」 - 「元に戻す(U)」)が使えません。

VBAマクロで実行した作業は、エクセルの「元に戻す」コマンドが利用できません。上の方法はVBAで実行してますので、たった1行の命令でも、元に戻す事ができなくなります。

「元に戻す」を利用する為には、エクセルに対して人間が操作してるように見せかけないといけませんので、キーの組み合わせで制御するようにします。

Application.SendKeys(Keys, Wait)

Key:エクセルへ送るキー
Wait:同期/非同期。省略可。

「行の挿入」は、コマンドメニューの「挿入() - 行(R)」にあります。VBAコードだと、下のようになります。

Application.SendKeys "%ir"

コマンドメニューにあるコマンドはほぼ全部キーボードで操作できるので、この方法はかなり有効だと思います。

ただ、画面がちらつく事があります。仕方ないので、諦めてください。

サンプルソースコード

[INSERT]キーで、行の挿入ができ、「元に戻す」も使えるようにします。

| | コメント (0) | トラックバック (0)

[VBA] Split関数テンプレート

Split関数は、ある文字列を区切り文字(デリミタ)として、それぞれの要素に分解してくれます。

たとえば、電話番号みたいな
「012-345-6789」 を 「012」「345」「6789」
の3つ に分解してくれます。

Split関数の戻り値はVariant型

Split関数を実行して戻ってきたデータは、Variant型です。内部的には、多分、文字列の1次元配列です。

返ってくるのは1次元配列ですが、VariantやStringの動的配列を宣言して、その変数に戻すとエラー「型が一致しません」になります。

Dim sDataArray() As Variant

sDataArray = Split("012-345-6789", "-", -1, vbTextCompare)

以下のように、単純なValiant型の変数を指定してあげましょう。

Dim sDataArray As Variant

sDataArray = Split("012-345-6789", "-", -1, vbTextCompare)

サンプルソースコード

Sprit関数の使い方テンプレート。
大体いつも、こんな風にして使ってます。

実行結果(イミディエイトウィンドウ)

| | コメント (1) | トラックバック (0)

[エクセル] 重複しない本当の順位を求める

Excelのランキング系関数は、重複するデータ(値)は同じ順位とみなされます。
なので、同率の順位が発生した場合、それ以降の順位がずれていきます。

100 100 90 80 70 70 60 50
って、データがあったとします。

順位は、

1位 100
1位 100
3位 90
4位 80
5位 70
5位 70
7位 60
8位 50

になります。

これを、

1位 100
2位 90
3位 80
4位 70
5位 60
6位 50

こういう順位にする方法ですが、かなり面倒みたいです。

アンドリューのエクセルTIPS:重複する値に騙されない

エクセルの関数は重複する値に関して致命的な欠点があります。 ・・・解説・・・

MoreFuncも入れてみたんですが、VBAから使う方法が分かりません。
英語のヘルプを読んでやってみましたが、「マクロが見つかりません」とか「型が一致しません」とか「Runメソッドは失敗しました」 になるので、あきらめました。

UniqueSmall

仕方がないので自作です。
Small関数の重複しないバージョンを作ってみました。

単純な発想で、「Smallへ渡すデータ範囲が重複なければいいはず」って考え方。
重複省く方法も、ソートしてから・・・とかしてません。
超手抜き。

文字コード Shfit_JIS(シフトJIS) です。
ブラウザで見る場合、右クリックでエンコードを切り替えてください。
初期状態でほぼ100%化けてると思います。

「UniqueSmall.txt」をダウンロード






| | コメント (1) | トラックバック (0)

[VBA] 動的配列 テンプレート

移動:[VBA] 動的配列 テンプレート

| | コメント (0) | トラックバック (0)