Excel VBAに効く Vitamin E |
|
ExcelではPHONETIC関数でCellに入力した漢字のフリガナを取得できますが、UserFormには同等の関数はありません。Win32APIを使ってUserFormでフリガナを取得する関数を作成しました。 |
|
|
 |
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のサブプロシジャを実行します。 |
|
|
1.Accessのインスタンスを作成します。
2.MDBを開きます。
3.MDBのサブプロシジャの引数に値を渡して実行します。
4.MDBは実行後渡された引数に新しい値をセットして終了します。
5.呼び元では返ってきた引数の値を参照します。 |
|
(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
としたほうが速くなります。
もっとも、どちらにしてもインスタンス取得に結構な時間がかかることに変わりはありません。 |
Source表示(上記TEXTファイル)
CallAccMdlのダウンロード(CallAccMdl.lzh 39k)
(UPD:01/09/08)
Excelのユーザフォームで進行状況ゲージを実行[97/2k]
|
 |
|
小技を使って%表示を青から白抜きに変えています。 |
|
(蛇足) |
ExcelとAccessのLabelコントロールの違い |
|
|
AccessのLabelコントロールでは、文字の途中にLabelコントロールのBorderがきた場合、文字は一部表示されます。

Excel(MicroSoftフォーム)のLabelコントロールではLabelコントロールのBorderにかかった文字は表示されません。

TextBoxでは文字の途中にTextBoxコントロールのBorderがきた場合、Excel・Accessとも文字は一部が表示されます。 |
Source表示
LsGaugeのダウンロード(lsgauge.lzh 12k)
(UPD:01/05/26)
Excelのメニューをリスティングする[97/2k]
ExcelとVBEのメニューをリスティングします。またそのメニューをVBAで実行する方法も学習します(^_^;)
Excel97と2kのリスト結果をシートに書き出してあります。
Source表示
vbe_MenuList.xlsのダウンロード(vbe_menulist.lzh 73k)
(UPD:00/10/28)
レジストリの値設定・値削除・キー削除を行なう[97/2k]
レジストリの値設定・値削除・キー削除を行ないます。
関数にしてありますのでSourseはVB(VB4では細工が必要です)やAccessでもそのまま使えます。 |
|
使用しているAPI |
|
|
RegCloseKey |
レジストリキーハンドルを開放 |
|
RegCreateKeyEx |
指定のキーを作成。存在する場合はキーをオープン。 |
|
RegDeleteKey |
指定のキーを削除 |
|
RegSetValueEx |
レジストリキーの値を設定 |
|
RegDeleteValue |
指定のキーの値名と値を削除 |
|
FormatMessage |
Windowsのエラーコードに対応したエラーメッセージ取得 |
放っておいても悪さはしないでしょうが、テストが終わったら作成したキーを削除しておいてください。くれぐれも既存のキーを削除しないようにご注意ください。サンプルであれば、
Software\Loadsystem\test
のうちテストで作成したキーはLoadsystem\testです。
Softwareキーは絶対に消さないでください。全てのアプリケーションが動かなくなります。
Source表示
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コントロールのアイコンを取り出す(その2)[97/2k]
Excel(Word)に登録されているFaceIdと16×16ピクセルのアイコンをすべてExcel表(Word文書)に書きだします。FaceIdは3000以上ありますので、5分以上かかると思います。嫌になったらCtrl+Brakeで中止してください。
(余談)
書きだしたアイコンをコピーして画像系のソフト(「OPTPiX webDesigner」、「IrfanView」など)にペーストしようとしても、クリップボードに実態がないためペーストできません。不思議なことにWindows付属のペイント(Pbrush.exe)にはペーストできます。私たちが覗いちゃいけない秘密のクリップボードがあるみたいです。もちろんペイントからコピーすれば画像系のソフトにペーストできます。
Source表示
FaceId.xlsのダウンロード
(UPD:00/09/23)
ファイル拡張子とプログラムを関連付ける[97/2k]
レジストリを書き換えてファイル拡張子をプログラムに関連付けます。FindExecutableの逆のAPIがないかと探し回りましたが甘かった、みーくんパパのアドバイスもあってゴリゴリやりました。「何でExcelなの?」とは聞かないでください、いろいろ事情があるのです。関数にしてありますのでSourseはVBやAccessでもそのまま使えます。
テストが終わったらエクスプローラで関連付けをはずしてください。
(生意気ですが、この意味がわからない方は使わないでください。)
Source表示
LsFileType.xlsのダウンロード
(UPD:00/09/09)
VB(A)のFormat関数について[97/2k]
*
|
Format関数 VS Val関数 どっちが速い |
|
環境 |
Intel Pentium 150 MHz
Windows 95 Version 4.00.950 |
|
Source |
Sub Format_VS_Val()
Const cVal = "123456"
Const kai = 100000
Dim l As Long
Dim TimerFomatStr As Single
Dim TimerFomatVar As Single
Dim TimerVal As Single
Dim varVal As Variant
TimerFomatStr = Timer
For l = 1 To kai
varVal = Format$(cVal)
Next l
TimerFomatStr = Timer - TimerFomatStr
TimerFomatVar = Timer
For l = 1 To kai
varVal = Format(cVal)
Next l
TimerFomatVar = Timer - TimerFomatVar
TimerVal = Timer
For l = 1 To kai
varVal = Val(cVal)
Next l
TimerVal = Timer - TimerVal
MsgBox "Format$=" & TimerFomatStr &
vbCr & _
"Format=" & TimerFomatVar
& vbCr & "Val=" & TimerVal
Debug.Print "Format$=" & TimerFomatStr
& vbCr & _
"Format=" & TimerFomatVar
& vbCr & "Val=" & TimerVal
End Sub
Sub Conv()
'カンマ編集された値の数値変換はFormat
Const cVal = "123,456"
MsgBox "値=" & cVal & vbCr & "val="
& Val(cVal) & vbCr & _
"Format=" & Format(cVal)
End Sub |
|
結果 |
Val |
4.340938 |
|
|
|
Format$ |
17.851560 |
|
|
|
Format |
18.290000 |
|
|
結論 |
1.Val関数の方がFormat関数の4倍速い
ただしカンマ編集された値を数値変換するにはFormatしかありません(Conv参照)
2.Format$の方がFormatより若干速い |
|
|
|
*
|
vbNullChar=Chr$(0)にFormat関数を使った場合 |
|
Source |
MsgBox Format(vbNullChar) = 0 |
|
結果 |
Excel97およびAccess97ではSRバージョンに関係なく、次のエラーが返ります。
実行時エラー '13':
型が一致しません。
(余談ですがVB4でも同様です。)
Excel2000およびAccess2000ではFormat関数がvbNullCharを返しますのでFalseになります。2000になってFormat関数は完全にValiant化されたということですか?
ちなみにVBEのバージョンは「Visual Basic 6.0」でした。 |
FormatVal.xlsのダウンロード
(UPD:00/09/09)
CommandBarコントロールのアイコンを取り出す[97/2k]
VBAメニューコントロールに貼る16×16ピクセルのアイコンを、Commandbarのコントロールから取り出します。コードはWord、Accessでも使えると思います。
Source表示
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を使っています。
Source表示
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をクリックすると2つ同時にクリックされてしまいます。


1.ListBox3をクリックします。
2.ListBox3をクリックした時に、Page2に切り替えます。
MultiPage1.Value = 1
3.Page2に切り替わりListBox4がクリックされColumnが選択てしまいます。当然ですがListBox3のColumn も選択されています。つまり同時に2つのオブジェクトが選択されます。
MSではこの現象を仕様だといってます。CommandButtonではこの現象は起きません。
原因はClickEventの発生するタイミングが違うためで、CommandButtonはMouseUpで、MultiPageはMouseDownでClickEventが発生します。ListBox1ではMouseUpのEventで
MultiPage1.Value = 1 を実行しているためこの怪現象は発生しません。
ちなみにAccessのタブコントロールではこの現象は発生しません。
Source表示
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のダウンロード
Source表示
【作成動機】
クライアントから「Excelのメニューバーが突然消えた!」との連絡があり急遽作成しメールで送りました。消えた原因はわかりません。クライアントはいつものように「私は何もしてない!」と力説しております(・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 等分できない場合は、うるう年になりません。
要約
1.金さん銀さんの誕生日はExcel関数では和暦にできない。(VBAでFormatを使うしかない)
2.1900年1月1日 〜 1900年2月29日のセル値をVBAから参照すると1日前になる。
3.すべての原因はLotusにある。MSは決して悪くない。
これは冗談ですが、せめてオプション設定できるようにして欲しいよネ。
Lotusにうるう年の計算できるやつが1人もいなかったの?ウソだろう〜。
それにしてもなんで今更Lotusなの?
Excelのセルをユーザ関数に渡した結果
西暦 |
和暦書式設定 |
曜日 WEEKDAY |
日数DATEDIF |
和暦Format |
1899/12/30 |
1899/12/30 |
#VALUE! |
#VALUE! |
明治 32年 12月 30日 |
1899/12/31 |
1899/12/31 |
#VALUE! |
#VALUE! |
明治 32年 12月 31日 |
1900/1/1 |
明治33年1月1日 |
1 |
1 |
明治 32年 12月 31日 |
1900/1/2 |
明治33年1月2日 |
2 |
58 |
明治 33年 1月 1日 |
1900/2/28 |
明治33年2月28日 |
3 |
1 |
明治 33年 2月 27日 |
1900/2/29 |
明治33年2月29日 |
4 |
1 |
明治 33年 2月 28日 |
1900/3/1 |
明治33年3月1日 |
5 |
40400 |
明治 33年 3月 1日 |
2010/10/10 |
平成22年10月10日 |
1 |
2607831 |
平成 22年 10月 10日 |
9150/10/10 |
平成7162年10月10日 |
3 |
|
平成 7162年 10月 10日 |
西暦(入力)
シートで唯一入力するカラムです。セルの書式を日付・西暦に設定しています。
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)
|