Ｅｘｃｅｌ ＶＢＡに効く Vitamin E

セキュリティ設定に関係なくVBAが書かれたExcelブックを開く[97/2k/2002/2003]（UPD:06/03/22）
	VBAが書かれたExcelブックを、セキュリティ設定された環境で開くと必ずメッセージが表示されます。VBSを使うとこのメッセージを回避できます。
Excel最適化[97/2k/2002/2003]（UPD:05/11/06）
	VBAの編集を繰り返すことで肥大化したExcelブックのゴミを取り除いてスリムにします。データだけのExcelブックには効き目がありません。アドインがインストールされますが、ソースを公開していますのでご利用ください。
ExcelのUserFormの「X」ボタンを無効にしたり、消したりします。 [97/2k/2002]（UPD:03/06/26）
ExcelのUserFormテキストボックスに入力した漢字のフリガナを取得[97/2k/2002]（UPD:03/01/16.17）

	ExcelではPHONETIC関数でCellに入力した漢字のフリガナを取得できますが、UserFormには同等の関数はありません。Win32APIを使ってUserFormでフリガナを取得する関数を作成しました。

Excel振込のソースコードの一部を公開（全銀データ項目の関連付け）[97/2k/2002]（UPD:02/12/07）
	読者のご要望にお応えし、Excel振込の全銀データ項目の関連付け部分のコードを公開します。
Excelのファイル名が２１８文字を超えると｢ファイル名が正しくありません｣のエラー[97〜2002]  （UPD:02/06/22）
	ヘルプファイルにはありませんが、MSサポート技術情では仕様としています。
フォルダを指定してフォルダ参照ダイアログ(SHBrowseForFolder)を開く[97〜2002] （UPD:02/06/18）
	WinAPIのSHBrowseForFolderを使って「フォルダ参照ダイアログ」を開きます。もちろんVB、AccessVBAでも使用できます。
シート上のコントロールのオブジェクト名は変えるな[97] （UPD:02/01/12）
	Excel97でシート上に貼り付けたコマンドボタン等のオブジェクト名を変更した場合、そのシートをコピーしてもオブジェクト名は移行されません｡（02/01/11MS確認）
UserFormのTerminateイベントでShowは使うな[97/2k] （UPD:01/10/24）
	UserFormのTerminateイベントでShowを使うと正常な動きをしなくなります｡（01/10/19MS確認）

ExcelのDialogsを使うとモーダルが解除される[97/2k]（UPD:01/10/13）
	ExcelのフォームからDialogを開くとExcelのモ−ダルが解除されます。つまり通常フォームを開いていればExcelシートには触れませんが、Dialogを開くと触ることが出来ます。
銀行カレンダー関数のExcelアドイン[97/2k]（UPD:01/09/29,01/12/15,02/09/19,2005/11/13）
	祝祭日に対応したカレンダー関数です。2003年からの「海の日」「敬老の日」のHappy Mondayにも対応済みです。
ExcelからAccessのプロシジャを実行する[97/2k]
	ExcelからAccessのサブプロシジャを実行します。
		１．Accessのインスタンスを作成します。 ２．MDBを開きます。 ３．MDBのサブプロシジャの引数に値を渡して実行します。 ４．MDBは実行後渡された引数に新しい値をセットして終了します。 ５．呼び元では返ってきた引数の値を参照します。
	（VBAとVBから同様の呼び出しが可能です。）
	'---------------------------------------------------------------------- '呼び出し元Excel '---------------------------------------------------------------------- Sub CommandButton1_Click() 　　'Accessを立ち上げ､ 'Test'というモジュールを実行します 　　'VBEメニューの「参照設定」で「Miclosoft Access 8.0 Object Library」（2000では9.0）をバインドする必要があります。 　　Dim appAccess As Access.Application 　　Dim a As String 　　'Accessのインスタンスを作成します 　　Set appAccess = New Access.Application 　　'カレントパスにあるCallAccTarget.mdbを開きます。 　　appAccess.OpenCurrentDatabase ThisWorkbook.Path & "\CallAccTarget.mdb", False 　　'CallAccTarget.mdbの標準モジュールにあるtestというサブプロシジャを実行します 　　'実行の際に引数に値を渡しています。 　　a = "(^o^)丿(^o^)丿(^o^)丿(^o^)丿(^o^)丿(^o^)丿" 　　appAccess.Run "test", a 　　'作成したAccessのインスタンスを閉じます 　　appAccess.Quit acQuitSaveNone 　　'testというサブプロシジャからかえってきた値を表示します 　　MsgBox a 　　'オブジェクト変数を開放します 　　Set appAccess = Nothing End Sub '---------------------------------------------------------------------- '呼び出されるAccessのモジュール '---------------------------------------------------------------------- Sub Test(prm As String) 　　'呼びもとのメッセージを表示します。 　　MsgBox "ファイル名＝" & CurrentDb().Name & vbCr & vbCr & "引数＝" & prm 　　'呼びもとにメッセージをかえします。 　　prm = "無事終了　(･_･)(･_･)(･_･)(･_･)(･_･)(･_･)(･_･)(･_･)(･_･)(･_･)" End Sub
	'---------------------------------------------------------------------- '呼び出し元Access '---------------------------------------------------------------------- Private Sub コマンド0_Click() 　　'別のAccessを立ち上げ､ 'Test'というモジュールを実行します 　　Dim appAccess As Access.Application 　　Dim wPath As String 　　Dim a As String 　　'新しくAccessのインスタンスを作成します 　　Set appAccess = New Access.Application 　　wPath = CurrentDb.Name 　　'最後に\が付いたカレントパスを取得します 　　wPath = Left$(wPath, InStr(wPath, Dir$(wPath)) - 1) 　　'カレントパスにあるCallAccTarget.mdbを開きます。 　　appAccess.OpenCurrentDatabase wPath & "CallAccTarget.mdb", False 　　'CallAccTarget.mdbの標準モジュールにあるtestというサブプロシジャを実行します 　　'実行の際に引数に値を渡しています。 　　a = "(^o^)丿(^o^)丿(^o^)丿(^o^)丿(^o^)丿(^o^)丿" 　　appAccess.Run "test", a 　　'作成したAccessのインスタンスを閉じます 　　appAccess.Quit acQuitSaveNone 　　'testというサブプロシジャからかえってきた値を表示します 　　MsgBox a 　　'オブジェクト変数を開放します 　　Set appAccess = Nothing End Sub
	（蛇足）	Newキーワードについて
		Dim appAccess As Object や Dim appAccess As Application よりも Dim appAccess As Access.Application のようにObjectを特定したほうが方が速く処理されます。 また Dim appAccess As New Access.Application よりも Dim appAccess As Access.Application Set appAccess = New Access.Application としたほうが速くなります。 もっとも、どちらにしてもインスタンス取得に結構な時間がかかることに変わりはありません。

Ｓｏｕｒｃｅ表示（上記TEXTファイル）

CallAccMdlのダウンロード(CallAccMdl.lzh 39k)

（UPD:01/09/08）

Excelのユーザフォームで進行状況ゲージを実行[97/2k]

Access版の進行状況ゲージは掲載しましたが、Excel版が必要になったので少しだけ改良を加え掲載します。

	小技を使って％表示を青から白抜きに変えています。
	（蛇足）	ExcelとAccessのLabelコントロールの違い
		AccessのLabelコントロールでは、文字の途中にLabelコントロールのBorderがきた場合、文字は一部表示されます。 　　 Excel(MicroSoftフォーム)のLabelコントロールではLabelコントロールのBorderにかかった文字は表示されません。 　　 TextBoxでは文字の途中にTextBoxコントロールのBorderがきた場合、Excel・Accessとも文字は一部が表示されます。

Ｓｏｕｒｃｅ表示

LsGaugeのダウンロード(lsgauge.lzh 12k)

（UPD:01/05/26）

Excelのメニューをリスティングする[97/2k]

ExcelとVBEのメニューをリスティングします。またそのメニューをVBAで実行する方法も学習します(^_^;)
Excel97と2kのリスト結果をシートに書き出してあります。

Ｓｏｕｒｃｅ表示

vbe_MenuList.xlsのダウンロード(vbe_menulist.lzh 73k)

（UPD:00/１0/28）

レジストリの値設定・値削除・キー削除を行なう[97/2k]

レジストリの値設定・値削除・キー削除を行ないます。 関数にしてありますのでSourseはVB(VB4では細工が必要です)やAccessでもそのまま使えます。

	使用しているAPI
	RegCloseKey	レジストリキーハンドルを開放
	RegCreateKeyEx	指定のキーを作成。存在する場合はキーをオープン。
	RegDeleteKey	指定のキーを削除
	RegSetValueEx	レジストリキーの値を設定
	RegDeleteValue	指定のキーの値名と値を削除
	FormatMessage	Windowsのエラーコードに対応したエラーメッセージ取得

放っておいても悪さはしないでしょうが、テストが終わったら作成したキーを削除しておいてください。くれぐれも既存のキーを削除しないようにご注意ください。サンプルであれば、
Software\Loadsystem\test
のうちテストで作成したキーはLoadsystem\testです。
Softwareキーは絶対に消さないでください。全てのアプリケーションが動かなくなります。

Ｓｏｕｒｃｅ表示

RegSetDel.xlsのダウンロード

（02/08/17）
レジストリの値削除で、動作方法を指定する定数が違っていました。Win9xでは動くのですが、Win XPでは KEY_ENUMERATE_SUB_KEYS の場合エラーになります。
変更した定数  KEY_ENUMERATE_SUB_KEYS --> KEY_SET_VALUE
rc = RegOpenKeyEx(RootKey, SubKey, 0, KEY_SET_VALUE, phkResult)

（UPD:00/10/07,02/08/17）

CommandBarコントロールのアイコンを取り出す(その２)[97/2k]

Excel(Word)に登録されているFaceIdと16×16ピクセルのアイコンをすべてExcel表(Word文書)に書きだします。FaceIdは3000以上ありますので、5分以上かかると思います。嫌になったらCtrl+Brakeで中止してください。
 (余談)
 書きだしたアイコンをコピーして画像系のソフト(「OPTPiX webDesigner」、「IrfanView」など)にペーストしようとしても、クリップボードに実態がないためペーストできません。不思議なことにWindows付属のペイント(Pbrush.exe)にはペーストできます。私たちが覗いちゃいけない秘密のクリップボードがあるみたいです。もちろんペイントからコピーすれば画像系のソフトにペーストできます。

Ｓｏｕｒｃｅ表示

FaceId.xlsのダウンロード

（UPD:00/09/23）

ファイル拡張子とプログラムを関連付ける[97/2k]

レジストリを書き換えてファイル拡張子をプログラムに関連付けます。FindExecutableの逆のAPIがないかと探し回りましたが甘かった、みーくんパパのアドバイスもあってゴリゴリやりました。「何でExcelなの？」とは聞かないでください、いろいろ事情があるのです。関数にしてありますのでSourseはVBやAccessでもそのまま使えます。

テストが終わったらエクスプローラで関連付けをはずしてください。
(生意気ですが、この意味がわからない方は使わないでください。)

Ｓｏｕｒｃｅ表示

LsFileType.xlsのダウンロード

（UPD:00/09/09）

VB（A）のFormat関数について[97/2k]

  FormatVal.xlsのダウンロード

（UPD:00/09/09）

CommandBarコントロールのアイコンを取り出す[97/2k]

VBAメニューコントロールに貼る16×16ピクセルのアイコンを、Commandbarのコントロールから取り出します。コードはWord、Accessでも使えると思います。

Ｓｏｕｒｃｅ表示

CommandBarIcon.xlsのダウンロード

（UPD:00/08/12）
(リンク先が違ってましたm(__)m　00/09/02)

WordのNormal.dotにプロシジャを追加する[97/2k]

VBAでWordのNormal.dotにAutoExecプロシジャを追加する方法です。Normal.dotを書き換える場面はあまりないかもしれませんが、Documentにプロシジャを追加する場合などに利用してください。
Normal.dotを書き換える前にバックアップを取りますが、FileCopyでは共有違反で失敗しますので、Win APIを使っています。

Ｓｏｕｒｃｅ表示

AddProc.docのダウンロード

（UPD:00/07/15）

ExcelのGetOpenFilenameで任意の初期フォルダを表示させる[97/2k]

Excelのファイルを開くダイアログ（Application.GetOpenFilename）はAPIを使わなくても済むので大変便利です。しかしダイアログが開いたときの初期値を任意のフォルダに設定する方法は準備されておりません。名前を付けて保存ダイアログ（Application.GetSaveAsFilename）では可能です。

　　 
                 ファイルを開くダイアログ（Application.GetOpenFilename）-Excel2000

そこで思いつくのがカレントフォルダをチェンジする方法です。

    ChDir "c:\Windows"
    Application.GetOpenFilename

Windowsフォルダが表示され成功です(^o^)丿

次にドライブを変えてみましょう。

    ChDir "a:\" 'フロッピードライブです。もちろんフロッピーはセットしておいてください。
    Application.GetOpenFilename

初期フォルダはWindowsフォルダのままです(-_-;)
MSに問い合わせたところ、Windows98ではフロッピードライブが表示されると言っていますが弊社のWindows95/98両方のマシンとも表示されませんでした。

そこでドライブが変わる場合は次の方法が有効です。

    ChDrive "a" 'フロッピードライブにドライブ変更してから
    ChDir "a:\" 'フォルダの変更を行ないます。
    Application.GetOpenFilename

ちなみにネットワーク環境では 、"\\NT_server\users"などのUNC表記はできませんので「ネットワークドライブの割り当て」を行なわなければなりません。

（UPD:00/06/24）

ExcelのMultiPageで重なり合ったListBoxをクリックすると・・・[97/2k]

ExcelのMultiPageで重なりあったListBoxをクリックすると２つ同時にクリックされてしまいます。

　　

                               

１．ListBox3をクリックします。
２．ListBox3をクリックした時に、Page2に切り替えます。
    MultiPage1.Value = 1
３．Page2に切り替わりListBox4がクリックされColumnが選択てしまいます。当然ですがListBox3のColumn も選択されています。つまり同時に２つのオブジェクトが選択されます。

MSではこの現象を仕様だといってます。CommandButtonではこの現象は起きません。
原因はClickEventの発生するタイミングが違うためで、CommandButtonはMouseUpで、MultiPageはMouseDownでClickEventが発生します。ListBox1ではMouseUpのEventで MultiPage1.Value = 1 を実行しているためこの怪現象は発生しません。
ちなみにAccessのタブコントロールではこの現象は発生しません。

Ｓｏｕｒｃｅ表示

MultiPage_Bug.xlsのダウンロード

（00/03/28 MS確認済み UPD:00/04/01）

Excelのメニューバーの表示/非表示を切り替える[97/2k]

Excelのメニューバー(Worksheet Menu Bar)の表示/非表示を切り替えます。

       

CmdBar.xlsをダブルクリックすれば「マクロ」の警告メッセージがでますので「マクロを有効にする」をクリックします。



のメッセージで表示/非表示を切り替えます。
もう一度切替を実行するには再度CmdBar.xlsを起動します ^_^;

【留意点】
・CommandBarオブジェクトのEnabledプロパティをTrue/Falseに設定すればメニューバーの表示/非表示が切り替わります。
・Visibleプロパティを変えようとするとエラーになります。
・Protectionプロパティをゼロに設定すればメニューバーを自由に移動できます。

同様の方法でAccessの場合もメニューバーの表示/非表示を切り替えることができます。

CmdBar.xlsのダウンロード

Ｓｏｕｒｃｅ表示

【作成動機】
クライアントから「Ｅｘｃｅｌのメニューバーが突然消えた！」との連絡があり急遽作成しメールで送りました。消えた原因はわかりません。クライアントはいつものように「私は何もしてない！」と力説しております(･o･)

（UPD:00/03/11）

Excelで1900年は閏年？

まず「マイクロソフト西暦 2000 年対応情報開示リソースCD（11月版）／\PRODUCT\USER_VIEW68307EN.HTM」からの抜粋

日付の処理

・保存 Microsoft Excel 97 は、1900 年 1 月 1 日を第 1 日目として、日付を数値で保存します。Microsoft Excel 97 が有効値として認識する日付の範囲は 1900 年 1 月 1 日 〜 9999 年 12 月 31 日です。Visual Basic と Visual Basic for Applications もこのシリアル日付方式を使用します。ただし、1900 年 1 月 1 日 〜 1900 年 2 月 28 日の日数が 1 日違います。これは、1900 年をうるう年として扱う Lotus 1-2-3 との下位互換用です。旧バージョンの Microsoft Excel が有効値として認識する日付の範囲は 1900 年 1 月 1 日 〜 2078 年 12 月 31 日です。
        (中略)
メモ: 1900 年はうるう年ではありません。ただし、テスト時に Microsoft Excel 97 が 1900 年をうるう年として扱っていることに気づかれるかもしれません。このアルゴリズムを採用した理由は、Lotus 1-2-3の日付との下位互換用であり、設計上そうなっています。西暦のうるう年の計算は、次のように行われます。ある年が 4 等分でき、100 等分できない場合は、うるう年になります。ある年が 100 等分でき、400 等分できない場合は、うるう年になりません。

要約
１．金さん銀さんの誕生日はExcel関数では和暦にできない。（VBAでFormatを使うしかない）
２．1900年1月1日 〜 1900年2月29日のセル値をVBAから参照すると1日前になる。
３．すべての原因はLotusにある。MSは決して悪くない。
これは冗談ですが、せめてオプション設定できるようにして欲しいよネ。
Lotusにうるう年の計算できるやつが１人もいなかったの?ウソだろう〜。
それにしてもなんで今更Lotusなの？

Excelのセルをユーザ関数に渡した結果

西暦（入力）
シートで唯一入力するカラムです。セルの書式を日付・西暦に設定しています。
1900年未満は文字列扱いになります。

和暦書式設定
セルの書式を日付・和暦に設定しています。
1900年未満は参照セルが日付でないため、和暦書式は適用されません。

曜日WEEKDAY
WEEKDAY関数で曜日を表示しています。
1900年未満は参照セルが日付でないため、エラー発生。

日数DATEDIF
DATEDIF関数で項目「西暦」の2つの日付間の日数を計算しています。
1900年未満は参照セルが日付でないため、エラー発生。

和暦Formatのユーザ関数
Function wareki(dt As Date) As String
　　　wareki = Format(dt, "ggg e年 m月 d日")
End Function 

この現象はExcelのいずれのバージョンでも発生します。仕様と明記してあるのでバグとはいえませんが、いつまでこの問題を引きずるのかMSサポートは明言を避けており、対応についてもVBA側でIf文を書いてくれとのことでした。
サンプルExcel表をダウンロード(date1900.xls 23Kb)(UPD:2000/01/07）
Excel VBAで1900年以前の年齢計算.xls（old1900.lzh 13Kb）(UPD:2002/07/03）

（1999/12/30 MS確認済み UPD:2000/01/07,15 2002/07/03）

VBAで30桁の数字は

Private Sub コマンド1_Click()
    Dim a As Variant, b As Variant, c As Variant

    a = 1.23456789012346E+19 '=12345678901234567890
    Debug.Print Format(a, "#,###")
    b = 1.23456789012346E+29 '=123456789012345678901234567890
    Debug.Print Format(b, "#,###")
    c = "987654321098765432109876543210"
    Debug.Print Format(Val(c), "#,###")
End Sub

結果
    a=12,345,678,901,234,600,000
    b=123,456,789,012,346,000,000,000,000,000
    c=987,654,321,098,765,000,000,000,000,000

結論
    先頭から15桁が有功数字で、16桁を丸めて計算します。

（UPD:2000/01/07）