【ワレコの講座】VBAをEXCEL2007,2010,2013,2016,2019の32/64 bit全対応させるには？【解決】

写真　ワレコのサイトを見ながらEXCEL VBAを猛勉強中の人

最近EXCELのVBAのコードを書いていて、VBAコードを

• EXCEL2007
• EXCEL2010
• EXCEL2013
• EXCEL2016
• EXCEL2019

の

• 32bit / 64bit

の全てに対応させるにはどうすれば良いのか分からなくなったので調べてみた。

当記事はその備忘録。

ちなみに、当記事は元々Gooブログに開設したワテのサイトで公開した以下の記事がオリジナルである。

VBAをEXCEL2007, 2010, 2013の32/64 bitの全てに対応させるにはどうすれば良いのか？解決！ - われこわれこ

当記事はワテの新ブログサイト、に引っ越した。以下は、その引っ越し前のオリジナル記事。参考までに残している。Gooでブログを始めた直後に書いた記事なので、HTMLの使い方も良く知らず、背景色が黒、文字が白と言うヘンテコなページになっている。最...

blog.goo.ne.jp

当サイトの記事はその記事に加筆訂正をしたものだ。

では、本題に入ろう。

VBAをEXCEL2007,2010,2013,2016,2019の32/64 bitの全てに対応させたい

インターネットで調べると、この件に関してはいろんな情報がヒットするが、やはりマイクロソフトの公式情報を確認するのが間違いないだろう。

Office 2010 の 32 ビット バージョンと 64 ビット バージョンとの互換性

learn.microsoft.com

 

この情報によると、

EXCEL2007は32bit版のみ

EXCEL2010から32bit版, 64bit版の二種類になった

EXCEL2010以降では、VBA7 と Win64 という 2 つの条件付きコンパイル定数が用意されている。

との事である。

つまり、EXCEL2010から導入されたVBA7という定数を使うとEXCELのバージョンが2007以前か2010以降かが区別できる。

一方、Win64は、実行しているEXCELのバージョンが64ビットなのか32ビットなのかの区別が出来る。

Win64という名前から、Windowsのビット数を表していると錯覚しがちだが、あくまで実行しているEXCELのビット数なので注意が必要だ。

従って、Windows7やWindows8の64bit版でEXCEL2010(32)を実行していると Win64は定義されない。

念のために以下に示す自作関数をいくつかの環境で試してみた。

EXCELのバージョンやビット数を調べるワテ自作VBA関数

Sub EXCELのバージョンやビット数を調べる_ワレコ版()

   #If VBA7 Then
      Debug.Print "EXCELは2010以上です。"
   #Else
      Debug.Print("EXCELは2007以下です。")
   #End If

   #If Win64 Then
      Debug.Print "EXCELは64bit版です。"
   #Else
      Debug.Print("EXCELは32bit版です。")
   #End If

End Sub

この自作VBA関数をワテが持っている幾つかのエクセル環境で実行してみた。

その結果を以下に示す。

Win7(64) EXCEL2013(32)での実行結果

EXCELは2010以上です。
EXCELは32bit版です。

Win7(64) EXCEL2003(32)での実行結果

EXCELは2007以下です。
EXCELは32bit版です。

Win8(64) EXCEL2013(64)での実行結果

EXCELは2010以上です。
EXCELは64bit版です。

WinXP(32) EXCEL2007(32)での実行結果

EXCELは2007以下です。
EXCELは32bit版です。

となった。

確かにエクセルのバージョンとビット数を正しく判定している。素晴らしい。

だれでも出来るか。

しかしまあ、それにしてもワテはこんなに沢山のエクセルを使ってエクセルアドイン（XLL、VSTO、VBA）の開発にアホみたいに猛烈に熱中していた時期があるのだ。

自称、EXCEL VBAの達人だ。いや変人だ。

VBAコンパイル定数をWin32API関数の切り替えに使う

さて、インターネットを検索すると、これらのコンパイル定数を使う場面としてWin32API関数をVBAから使う例が良く出てくる。

 

例えば、VBAの中でレジストリを操作したい場合には、従来の32bitエクセル環境では、以下のような行をVBAのModuleの先頭に書いて使っていた。

Declare Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long

この関数のC++での定義は以下のようになるが、

LONG RegOpenKeyA ( HKEY hKey, LPCSTR lpSubKey, HKEY *phkResult );

この中にはポインタ型の引数LPCSTR lpSubKeyがあり、それは32bitのOSでは32bit長であるが64bitOSなら64bit長になる。

従って、このDeclare Function宣言文をExcel 2010以降の64bit版で使う場合には、

Declare PtrSafe Function RegOpenKeyA Lib "advapire32.dll" (ByVal hKey as LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long

のように書くようになったらしい。

このLongPtr というのがEXCEL2010で新たに導入された”ポインター” データ型だ。

また

Declare ステートメントで PtrSafe を指定しないと、64 ビット バージョンの Office 2010 と互換性がないものと見なされます。

引用元　https://docs.microsoft.com/ja-jp/office/client-developer/shared/compatibility-between-the-32-bit-and-64-bit-versions-of-office

ということなので、つまりPtrSafeをDeclareの後ろに付けておくと64bitエクセル対応の関数と言うことだ。

なお、

PtrSafe 属性は、32 ビット バージョンの Office ではオプションです。これにより、既存の Declare ステートメントは従来どおりに動作します。

引用元　https://docs.microsoft.com/ja-jp/office/client-developer/shared/compatibility-between-the-32-bit-and-64-bit-versions-of-office

との事なので、EXCEL 2010以降を使っているなら、それが32ビットでも64ビットでも兎に角必ずPtrSafeを付けておけば良いと言う事だ。

つまりまあ、64ビット版API関数は、EXCEL初の64ビット版EXCEL2010と共に登場した訳だから、64ビットEXCELで使えるのは当然だが、32ビットEXCELでも使える設計になっている訳だな。ただしそれはEXCEL2010以上の場合だ。

もしEXCEL2007以前（全て32ビットEXCEL）でAPI関数を使うなら、従来版の32ビットAPI関数を使うべきだろう。今までその組み合わせで使って来ている訳だから。

まあ、ここまではワテもすんなりと理解出来た。

では、いよいよエクセルの各種バージョン（EXCEL2007~2019）や32bit・64bitのどれを使っていても、Declare PtrSafe Function宣言の正しい記述方法を探ってみよう。

32bitと64bit環境での宣言の切り替え例（マイクロソフト公式）

上記のマイクロソフトのサイトでは、この32bitと64bit環境での宣言の切り替えの例として以下のVBAコード例が掲載されている。

#If Win64 Then
   '64ビット版EXCEL
　　Declare PtrSafe Function MyMathFunc Lib "User32" (ByVal N As LongLong) As LongLong '(1)
#Else
   '32ビット版EXCEL
　　Declare Function MyMathFunc Lib "User32" (ByVal N As Long) As Long '(2)
#End If


#If VBA7 Then
   'EXCEL2010以上 
　　Declare PtrSafe Sub MessageBeep Lib "User32" (ByVal N AS Long) '(3)
#Else
   'EXCEL2007以下
　　Declare Sub MessageBeep Lib "User32" (ByVal N As Long) '(4)
#End If

これは、分かりにくいと思う。

前者のWin64の有無で(1)(2)を切り替えるのは分かる。

つまり、64bitのEXCELで実行した場合は(1)を使い、32bitのEXCELで実行した場合は(2)を使うということだ。

 

では、後者のVB7の有無で(3)(4)を切り替えているがこれは何だ？

これはEXCEL2010以降を使っているなら(3)を使い、EXCEL2007(32bit版しかない)を使っている場合は(4)を使うということになる。

まあエクセルのバージョンで切り替えている訳だ。

つまり、EXCEL2010以降を使っているならそれが64bitか32bitかにかかわらず(3)を使い、EXCEL2007(32bit版しかない)を使っている場合は(4)を使うということになる。

ではEXCEL2010の64bitを使った場合にはどうなるのか。

それは(3)が使われる。PtrSafeが付いているので64bit対応ということだ。

なんだかややこしいぞ。

32bit/64bitで区別するか、EXCEL2007以下/2010以上で区別するか

要するにエクセルのバージョンを区別するVBA7定数なんて使わずに、エクセルのビット数を区別するWin64定数のみを使って以下のように場合分けすれば良いのではないのか？

#If Win64 Then
　　'64ビット版EXCEL
　　Declare PtrSafe Sub MessageBeep Lib "User32" (ByVal N AS Long) '(5)
#Else
　　'32ビット版EXCEL
　　Declare Sub MessageBeep Lib "User32" (ByVal N As Long) '(6)
#End If

 

この件に関してインターネットを検索していると、32bitと64bitとでDeclare文の切り替えは、

#If VBA7 And Win64 Then
　'64ビット版
#Else
　'32ビット版
#End If

と言うふうに

#If VBA7 And Win64 Then　'EXCEL2010以上 And 64bit版 なら

と記述してある例をよく見る。

でも、これも何か変だぞ。

なぜならこれだと「EXCEL2010以上で64bit版なら」と言う事になるが、64bit版があるのはEXCEL2010以上と決まっている訳だからWin64に加えてVBA7を使うのは冗長で無駄な判定のような気がする。

要するに32bit/64bitで区別するのか、EXCEL2007以下/2010以上で区別するのかの問題である。

分からないのでExcel MVP の Jan Karel Pieterseさんに質問してみた

まあ、それは好き好きじゃないかなあと思うが、ワテの場合は32bit/64bitで区別するほうが分かりやすいような気がするのだが。。。

 

しばらく考えていて自分でも混乱してきたのだが、ようやくその答えが分かった。

それは、上記のマイクロソフトのサイトの中で紹介されている

Declaring API functions for 64 bit Office (and Mac Office)

JKP Application Development Services, Microsoft Office Application Development

www.jkp-ads.com

のページにあった。

このページは、Excel MVP の Jan Karel Pieterse 氏の サイトだ。

そこにはWin32API関数のDeclare宣言文が沢山記載されていて、32bit/64bit対応したものの一覧がある。

基本はVBA定数（2007以下･2010以上）で切り替えるのが良い

その中の殆どのDeclare宣言文は32bit/64bitの両方の関数定義が書かれているので、やはりそれらを

#If Win64 then

#Else

#End If

で使い分ければいいと思ったのだが、自信が無いので Jan Karel Pieterse 氏本人に質問してみた。

そしたら、

Hi Wareko,

VBA7 suffices in almost all cases. In case there is an exception it is mentioned in the sample code above.

こんにちは、ワレコさん、

ほとんどの場合、VBA7で十分です。 例外がある場合は、上記のサンプルコードに記載されています。

というお返事を頂いた。翻訳はGoogle翻訳を使った。

なので、殆どの場合、VBA7定数を使って以下のように

#If VBA7 then　
   'EXCEL2010以上
#Else
   'EXCEL2007以下
#End If

切り替えればよいというアドバイスなので、私もそうしようと思う。

なので、ネットでよく見かける

#If VBA7 And Win64 Then

と書く必要は必ずしも無いのだ。

恐らく、 Jan Karel Pieterse 氏のサイトを見た人は、ワテと同じくVBA定数やWin64定数のどれを使えば良いのかを Jan Karel Pieterse氏に質問しているのだと思う。

その質問に対する回答は、Jan Karel Pieterse 氏のサイトの末尾に分かり易くまとめてあったので以下に引用させて頂く。

Conditional compiling（条件付きコンパイル）

以下の英文は、Jan Karel Pieterse 氏の サイト

https://www.jkp-ads.com/articles/apideclarations.asp

から引用させて頂いた。

原文	Google翻訳
If your code needs to run on both 32 bit and 64 bit Excel, then another thing to do is add conditional compilation to your VBA.	コードを32ビットと64ビットの両方のExcelで実行する必要がある場合は、もう1つ行うべきことはVBAに条件付きコンパイルを追加することです。
Microsoft devised two compile constants to handle this:	Microsoftはこれを処理するために2つのコンパイル定数を考案しました。
VBA7: True if you’re using Office 2010, False for older versions	VBA7：Office 2010を使用している場合はTrue、古いバージョンの場合はFalse
WIN64: True if your Office installation is 64 bit, false for 32 bit.	WIN64：Officeのインストールが64ビットの場合はtrue、32ビットの場合はfalseです。
Since the 64 bit declarations also work on 32 bit Office 2010, all you have to test for is VBA7:	64ビット宣言は32ビットOffice 2010でも機能するため、テストする必要があるのはVBA7だけです。

つまり、 自作VBAコードを32bitと64bitの両方のExcelに対応させるには、Jan Karel Pieterse 氏推薦の記述方法は以下の通り。ワテの日本語注釈あり。

#If VBA7 Then

   'VBA7はEXCEL2010以上を意味するが、EXCEL2010以上ならPtrSafeは32/64どちらも対応しているので、
   '結局、この#IFブロックではEXCEL2010以上の32/64全てに対応できる。
   Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long

#Else

   'こちらの#ElseブロックはEXCEL2007以下で実行される。それは32ビット版のみなのでPtrSafeを付けてはいけない。
   Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long

#End If

そして、

And then in the routine where this function is put to use:

そして、この関数が使われるルーチンの中で：

以下のように関数を使えば良い。

#If VBA7 Then
   Dim hDC As LongPtr
#Else
   Dim hDC As Long
#End If
Dim lDotsPerInch As Long
'Get the user's DPI setting
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)

と言う事になる。

なるほど。

Win64とVBA7の両方の定数を使って切り替える例も幾つかある

例外としては、Jan Karel Pieterse 氏のコメントにあるように、

SetWindowLongPtr

などの関数ではWin64とVBA7の両方を使う必要がある。

つまり、Jan Karel Pieterse 氏のコメントに以下の記載がある。

This is one of the few API functions that requires the Win64 compile constant:

これは、Win64コンパイル定数を必要とする数少ないAPI関数の1つです。

従って、SetWindowLongPtrの場合には以下のように記述するのだ。

#If VBA7 Then
   #If Win64 Then
      Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
   #Else
      Private Declare Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
   #End If
#Else
   Private Declare Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If

つまりまあ、

#If VBA7 Then
   #If Win64 Then
      'EXCEL2010以上で64ビットなら
   #Else
      'EXCEL2010以上で32ビットなら
   #End If
#Else
   'EXCEL2007以下なら（64ビットでも32ビットでもだが、32ビット版しか無い）
#End If

と言う事か。

なるほど。

 Jan Karel Pieterse 氏のサイトでは、このSetWindowLongPtr関数以外にも、Win64とVBA7を使ってややこしい切替が必要になる関数が幾つかある。

最後にワテ流にまとめてみる

Jan Karel Pieterse 氏のサイトでは、API関数の宣言文が32 bit版と64 bit版の順に記載されている。

当初は、ワテは、それを見て、

#If Win64 then
   '64ビット版EXCEL向けに
   '64ビット版API関数宣言文をここに貼る❶
#Else
   '32ビット版EXCEL向けに 
   '32ビット版API関数宣言文をここに貼る❷
#End If

コード　ワテ版条件付きコンパイル

のように場合分けすれば良いと思った。

でも、Jan Karel Pieterse 氏の説明では、

#If VBA7 then　
   'EXCEL2010以上
   '64ビット版API関数宣言文をここに貼る❸
#Else
   'EXCEL2007以下
   '32ビット版API関数宣言文をここに貼る❹
#End If

コード　Jan Karel Pieterse 氏版条件付きコンパイル

が良いとの事なので、ワテもそうする予定だ。

ワテ方式でも動くのか、あるいは、ワテ方式では動かない場合があるのかは未確認だが。

ワテ方式では、EXCEL2010以降の32ビット版を動かした場合に❷が使われるがそれは、EXCEL2007までで使っていた古い32ビットAPI関数だ。

でもEXCEL2010で使うならより新しい❸が良いと言う事だろう。

❸はDeclare PtrSafe Functionと宣言されているので64ビットだけでなく32ビットでも使えるのだから。

要するに64ビット版API関数は、EXCEL初の64ビット版EXCEL2010と共に登場した訳だから、64ビットEXCELで使えるのは当然だが、32ビットEXCELでも使える設計になっている訳だ。ただしそれはEXCEL2010以上の場合。

もしEXCEL2007以前（全て32ビットEXCEL）でAPI関数を使うなら、従来版の32ビットAPI関数を使うべきだろう。今までその組み合わせで使って問題無く使えている訳だから。

 

では、ネットで良く見かける以下の条件分けはどうなのか？

#If VBA7 And Win64 Then　
   'EXCEL2010以上の64ビット版
   '64ビット版API関数宣言文をここに貼る❺
#Else
   'EXCEL2007以下の32ビット版やEXCEL2010以上の32ビット版（要するに32ビットEXCEL全部）
   '32ビット版API関数宣言文をここに貼る❻
#End If

コード　ネットで良く見かける条件付きコンパイル

この場合にも、EXCEL2010以降の32ビット版を動かすと、EXCEL2007までに使っていた古い32ビットAPI関数❻が使われる。

それでも動くとは思うが、でもやはり、EXCEL2010（32ビット）以降に正式対応している32ビット版API❺を使うほうが良いと思うのだが。

と言う事で、やはり、Jan Karel Pieterse 氏の説明通り、通常はVBA7定数で条件分けするのが良いだろう。

まとめ

と言う事で、当初の目的である、

「VBAをエクセルの各種バージョンや32bit/64bitの全てに対応させるためのWin32API関数の宣言のやり方」

は、Excel MVP の Jan Karel Pieterse 氏のサイトを見て、

Declaring API functions for 64 bit Office (and Mac Office)

JKP Application Development Services, Microsoft Office Application Development

www.jkp-ads.com

必要な関数をコピペして使えば良いと言う事だ。

条件コンパイル定数は通常はVBA7だけ使えば良い。

と言う訳で、紆余曲折したが、ワテの疑問は解消した。

ネットには多数の情報があるが、間違った情報もあるので単純に鵜呑みするのは危険だ。

つまりまあ、このワテのサイトの情報も正しいとは限らないので、注意が必要だ。

自称、EXCEL VBAの変人だ。

エクセルの本を読む

この本は講談社の有名なブルーバックスシリーズだ。2012年4月刊で、現在も改訂されて出版が続いている人気の本だ。アマゾンのレビューでも高評価だ。

小型の本なので通勤通学電車の中で読めるので、ビジネスマン必読の一冊だ。

ちなみに、ワテは読んでいない。

なんでやねん！

こちらの本は、EXCEL関連本で本日の時点でアマゾンランキング第一位だ。

ちなみに、ワテは読んでいない。

あかんがな。

全てのMicrosoft Excel の 売れ筋ランキングをみる