Excel VBAに効く 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のファイル名が218文字を超えると「ファイル名が正しくありません」のエラー[97〜2002]  (UPD:02/06/22)
ヘルプファイルにはありませんが、MSサポート技術情では仕様としています。
フォルダを指定してフォルダ参照ダイアログ(SHBrowseForFolder)を開く[972002] (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のサブプロシジャを実行します。
  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]

Access版の進行状況ゲージは掲載しましたが、Excel版が必要になったので少しだけ改良を加え掲載します。
小技を使って%表示を青から白抜きに変えています。
(蛇足) 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)