概要
ExcelVBA開発時にハマった点・不可解な点を列挙します。ググったらすぐ解決する問題は極力避けつつ、致命的なものを優先します。
なおサンプルコードには日本語を使用していますが、実際には半角英数を用いてください。
環境
基本的にはExcel 2016 たまにExcel 2010
シート関連
保護されたシートの書式を上書きされる
シートを保護しつつセルの入力を可能とする場合、対象セルのロックを外すことで入力が可能となる。
この状態では通常セルの書式を変更することはできないが、セルコピー＆貼り付けを行った場合はコピー元の書式で貼り付けが可能となる。貼り付け先が結合セルの場合はその結合も解除される。当然入力規則で設定したリストなどもクリアされる。
対処方法
なし。
改行を含む文字列をセルへ貼り付けると自動で折り返し表示となる
セルのコピーペーストに限らず、コードからRange.Value = hogeとした場合も同様。
改行を含む文字列をセルに貼り付けを行うと、自動でセルの書式が「折り返して全体表示」となる。
対処方法
貼り付け時に防ぐ手段はないないため、貼り付け後に再度セルの書式を変更する。
'セルへの代入
Range("A1").value = "こん" & vbcrlf & "ちわ" '
Range("A1").WrapText = False
シート上の図形の位置がずれる
シートの表示倍率を変更すると図形の位置がずれて表示される。
対処方法
図形が「セルに合わせて移動やサイズ変更をしない」となっているとズレが顕著になる。
問題がなければ「セルに合わせて移動するがサイズ変更はしない」に変更する。
ActiveXコントロールのフォントがなんか変
コマンドボタンなどのActiveXコントロールをシート上に設置すると、フォントサイズが画面の解像度に応じで変更される。
対処方法
シートにActiveXコントロールを直接置かないようにする。
ユーザフォームに設置するか、フォームコントロール・図形で代用する。
リンクされた図が存在すると処理が遅くなる
リンクされた図が存在すると処理ごとに再描画されるため、遅くなる。
この描画は再計算を停止しても効果がない。
対処方法
マクロ実行中などはリンクされた図のリンクを解除する。
Dim リンク図 as Picture,リンク先 as String
リンク先 = リンク図.Formula
リンク図.Formula = "" 'リンクの解除
Call マクロ実行
リンク図.Formula = "=" & Trim(リンク先) '余計な空白が入るので除去
ファイルオープン時に修復が実行される
修復されたレコード: /xl/sharedStrings.xml パーツ内の文字列プロパティ (文字列)
数式を「表示しない」としたセルの値を変更して保存すると発生する場合がある。
「表示しない」としたセルへは基本的に数式のみをセットする。
保護されたセルの結合が解除される
以下のような状況の場合、シートが保護されていてもセルの結合が解除される。
- 行の削除を許可した状態でシートを保護
- ロックされたセルおよび結合セルを含む行の削除を試みる
対処方法
案としては行削除を禁止して「行削除ボタン」を追加する、そもそもセル結合しないなど。
根本的な解決法はない。
行のコピーペースト時に「コピー領域と貼り付け領域のサイズが違うため...」と表示される。
以下の場合に発生する。
- 貼り付け先のシートでフィルタによる絞り込みが行われている。
- コピー元、貼り付け先は別のシートであり、一方のシートのみ非表示となっている列が存在する。
対処方法
- 貼り付け先の絞り込みを解除する。(絞り込まれた状態はもいろいろ挙動が不審なので制約がなければこれが推奨)
- 行貼り付けの際には、対象の行ではなく開始セル(A列のセル)を選択する。
セルに数式を追加すると表示形式が「文字列」などへ勝手に変わる
入力した数式が別のセルを参照するだけの場合、その参照先の書式へ変更される。
対処方法
=A1 ではなく =A1 & ""のように指定する。
オートフィルタで並べ替えを行うとクラッシュする。
画面上にオートフィルタが存在する状態で以下を実行する。
Sub 名前の全削除
For Each n In Names
n.Delete
Next
End Sub
実行後、画面上のフィルタを操作するとクラッシュする。
対処方法
オートフィルタに必要な名前が削除されていることが原因(シート名!_FilterDatabase)
この名前を削除しないようにするか、フィルタの削除⇒再設定を行う。
条件付き書式の背景色を「色なし」へ設定できない。
条件付き書式のセル背景色を設定する際、「色なし」を選択しても背景色が塗りつぶされている。
対処方法
一旦「クリア」する。「色なし」ボタンは正しく機能していないため押してはいけない。
(画面上の操作ではformatCondition.Interior.ColorIndexへxlColorIndexNoneをセットしているが、条件付き書式の場合はNullをセットする必要があるっぽい)
ActiveXコントロールのvisibleが勝手にTrueになる。
ボタンなどのActiveXコントールでVisible=Falseとしても、「デザインモード」としたままファイル保存⇒終了すると次回オープン時にVisible=Trueとなっている。
VBA関連
**[タイトル変更]**WindowsUpdate未実施の場合、Excel保存時にクラッシュする
**(旧タイトル)**マイナーバージョンの違いでエラーとなる(Excel2016)
一部の環境において特定のExcelファイルが保存時にクラッシュする現象が発生していたが、おそらくコイツのせい。
Office 2016 (KB3114369) の 2016 年 5 月 3 日更新
Excel本体(ver 16.0.xxxx.0000)ではなく**VBE7.dll(ver 7.01.xxxx)**の更新が必要となる。
対処方法
WindowsUpdateを実行する。 実際には宗教上の理由でUpdateができないと思われるのでエラーの原因をマクロから除く必要があるが、現時点ではその原因がわからない・・・😂。
ただ、Update未実施の端末でコンパイルしたファイルはUpdate実施済みの端末でも実行可能なため、
- デコンパイルツールを用いてコンパイル前の状態に戻す。
- Update未実施の端末でコンパイルして保存。
でひとまず対応可能。以下、エラーの原因調査メモ
- 特定のシートに見た目ではわからない不具合がある?
- シートモジュールで変数宣言しているため?
- シートモジュールを外部から呼び出すため?
- プログラム中にCodeNameを用いているため?
画像のコピー時にエラーとなる
画像のコピー(Shape.copyなど)はクリップボードへ格納する処理を行う。
しかしこの処理は投げっぱなしで処理が正しく終了したかは判断しないため貼り付け時にエラーとなる場合がある。
対処方法
以下のいずれか。
A.コピーペースト処理を冗長化する。
コピーペースト時にエラーが発生するものとして処理を書く。
'クリップボードを空にするにはクリップボード関連のあれこれが必要
'Public Declare Function EmptyClipboard Lib "USER32" () As Longとか
'Shapeのコピー処理
Public Function CopyShapeToClipboard(shp As Shape) As Boolean
Call クリップボードを空にする処理
On Error Resume Next
Do
Do
err.Clear
shp.Copy
Doevents
Sleep (10)
Doevents
Loop While err.number <> 0
Loop While Application.ClipboardFormats(1) = True
End Function
B. セルコピーで画像もコピーする。
Range.Copyもクリップボードを経由するため同様の問題が発生するが、引数に貼り付け先を指定した場合はコピーペーストの成功が保証される。(本当に大丈夫か？)
'オプションの「挿入したオブジェクトをセルとともに切り取り、コピー、並べ替えを行う」にチェックが必要
'Application.CopyObjectsWithCells = True
dim srcRng as Range,dstRng as Range
srcRng.Copy dstRng 'srcRngに含まれる画像もコピーされる。
コード中に日本語が含まれているとコードが動かない
ExcelVBAの場合関数名などにも日本語が利用できるが、環境によっては動作しなくなる。
'日本語環境では動作するが、海外ではエラーとなる
Public Function こんちはを返す関数 as String
Dim ほげ as String 'ほげ
ほげ = "こんちは"
こんちはを返す関数 = ほげ
End Function
対処方法
VBE(エディタ)が日本語版ではSJISのため、海外では2バイト文字が文字化けする。
コメントはそのままでもよいが、それ以外の2バイト文字を取り除く。
'定数などはセルに書き込んでおく。
'海外でも動く
Public Function ReturnHello as String
Dim hoge as String 'ほげ
hoge = ThisWorkbook.Worksheets(1).Range("A1").Value '対象セルに値を入れておく
ReturnHello = hoge
End Function
保護ビューを解除するとマクロのエラーが発生する
メール等で入手したExcelファイルはデフォルトで保護ビューとなるが、これを解除した際にマクロがエラーとなる場合がある。
Workbook_Openイベントは通常ブックを開いたとき(マクロを有効にしたとき)に実行される。
ファイルが保護ビューの場合はそれが解除される前に実行されるため、エラーとなる。(保護ビュー下では基本的にマクロは実行できない)
対処方法
MSの実装例に従う。Workbook_openではなくWorkbook_Activateで実行する。
Option Explicit
Public WithEvents oApp As Excel.Application
Private bDeferredOpen As Boolean
Private Sub oApp_WorkbookActivate(ByVal Wb As Workbook)
If bDeferredOpen Then
bDeferredOpen = False
Call WorkbookOpenHandler(Wb)
End If
End Sub
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Dim oProtectedViewWindow As ProtectedViewWindow
On Error Resume Next 'The below line will throw error (Subscript out of range) if the workbook is not opened in protected view.
Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(Wb.Name)
On Error GoTo 0 'Reset error handling
If oProtectedViewWindow Is Nothing Then
bDeferredOpen = False
Call WorkbookOpenHandler(Wb)
Else 'Delay open actions till the workbook gets activated.
bDeferredOpen = True
End If
End Sub
Private Sub WorkbookOpenHandler(ByVal Wb As Workbook)
'The actual workbook open event handler code goes here...
End Sub
**(旧)従来の内容**
Workbook_Openに処理が存在する場合は、自身が保護ビューがどうか判定を行う。
Private Sub Workbook_Open
'自身が保護ビューとなっているか判定
For Each ウィンドウ In Application.ProtectedViewWindows
If ウィンドウ.caption = ThisWorkbook.Name Then
is保護ビュー =True
End if
next
'実際の処理~
'If is保護ビュー then ・・・
End Sub
Workbook_BeforeSaveでエラーが発生する
Workbook_BeforeSaveはExcelファイルの保存前に実行されるが、このとき以下の条件の場合はエラーが発生する。
- 手作業ではなくコードから保存をする。(ThisWorkbook.Save など)
- 特定のコードを呼び出す(Range.CurrentRegionなど多数)
対処方法
コード中で保存処理を行う場合はWorkbook_BeforeSaveを呼ばないようにする。
Application.EnableEvents =False
Call 保存前の処理
ThisWorkbook.Save
Call 保存後の処理
Application.EnableEvents =True
Application.Interactiveの罠
マクロ実行中は操作されないようにApplication.Interactive=Trueとする場合があるが、以下のような現象が発生する。
- ステップ実行時にApplication.Interactive=trueが反映されない
- 別のブックをアクティブとしてもマクロ終了時に元のブックに戻る
- Userformを閉じる際に別のウィンドウが全面に表示される。
ファイル選択ダイアログの出現位置が変わる
Application.FileDialogでファイル(フォルダ)の選択が可能だが、このダイアログの位置が呼び出すたびに異なる。
対処方法
ダイアログのタイトルを統一する。
ダイアログはタイトルごとに表示位置・最大化等を記憶しているので、別のタイトルを設定すると異なる位置に出現することになる。
セルへ配列代入時に絞り込みが行われていると正しく貼り付けられない
セルに値を転記する際、通常は速度面からセル一つずつではなく配列で一気に代入する。
'セルへの書き込みサンプル
Redim arr(1 to 4,1 to 1) as Variant '代入用配列
for i=1 to 4
arr(i,1) = i
next
'選択中のセルへ張り付け
Selection.resize(UBound(arr,1),UBound(arr,2)) = arr
しかし貼り付け先がオートフィルタで絞り込まれていた場合、正しく貼り付けることができない。
対処方法
処理時のみ絞り込みを解除するか、セル一つずつ代入する。
絞り込みの状態を取得するのは面倒なので、元の状態に戻す必要があるならば時間はかかるがセル一つずつの方が確実。
(64bitのみ)IF文の条件式が常にTRUEとして扱われる
以下の条件の場合に現象が発生
- IFの条件式に関数の戻り値を利用
- Optionalで指定した引数をNewする
- 実態を明示的に破棄しない
- デストラクタでDB切断処理が走る
'実際にはTRUEが表示される。
Public Sub FALSEが表示されるはずの関数
IF (FALSE関数) then
debug.print "TRUE"
ELSE
debug.print "FALSE"
END IF
End sub
'常にFalseを返すはず
Private Sub FALSE関数(optional db as DB接続クラス) as boolean
if db = Nothing then set db = new DB接続クラス
FALSE関数 = false
End Sub
'New時にDB接続し、破棄時に切断する。
Private Sub Class_Initialize
'ADO接続開始処理~
End Sub
Private Sub Class_Terminate
'ADO切断処理~
End Sub
対処方法
上記条件のどれかを外す。
'後処理をきちんとすれば問題ない
Private Sub FALSE関数(optional db as DB接続クラス) as boolean
if db = Nothing then
set db = new DB接続クラス
dim 破棄する as boolean : 破棄する = True ' 引数なしなら終了時に破棄
end if
FALSE関数 = false
IF 破棄する then Set db = Nothing
End Sub
ステップ実行時に「中断モードでコードを実行することができません」と表示される
ステップ実行時やエラーでのマクロ中断時に別のブックを開いた状態で停止すると、「中断モードで～」のエラーが表示される場合がある。
'ステップ実行する
Public Sub 中断エラー()
Dim wb As Workbook
Set wb = Workbooks.Open("中断エラー.xlsm")
'ここで止めるとエラー発生
wb.Close
'ここで止めてもエラーとならない
End Sub
原因は対象のExcelファイルにCustomUIを設定しているため。
CustomUIを定義することでリボンを拡張することができるが、ステップ実行時にはそのイベントが発火するためエラーとなる。
「onLoad」など特定のイベントが設定されていない場合は問題なく実行できる。
<!--customUI_onloadやtab_getVisibleはステップ実行時にエラーを吐く-->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="中断エラー.xlsm!OnLoad">
<ribbon startFromScratch="false">
<tabs>
<tab id="customTab" label="Custom Tab" getVisible="中断エラー.xlsm!Tab_getVisible">
<group id="customGroup" label="Custom Group">
<!--onActionなどはOpen時に実行されないのでエラーとならない-->
<button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="中断エラー.xlsm!Tab_onAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
cumtomUIを設定する場合はxmlを直接編集することもできますが、Office Custom UI Editorを利用すると比較的簡単ですのでおすすめです。
対処方法
なし。開発がしにくくなるだけ(本番環境ではVBEが表示されることはないはず?)なので耐える。
AddinでCustomUIを設定している場合も多いので、可能ならそのアドインを外す。
入力規則のリストが表示された状態で動作するマクロについての問題
入力規則に設定されたリストを表示している場合には通常マクロは実行されないが、例えば対象セルを編集モード(F2)とした状態でリスト表示を行うと、Worksheet_Changeイベントが発火する。
この際、一部機能(主にセル関連?)が正しく動作しなくなる。
Private Sub Worksheet_Change(ByVal Target As Range)
Union(Me.Range("A1"), Me.Range("B1")).Select 'Union,Intersect,UsedRange等々...
End Sub
入力規則のリストへ255文字以上指定した場合にシートが破損する。
データの入力規則へは最大255文字しか入力できない。
しかし、Validationオブジェクトを直接操作するとそれ以上の文字数でも設定できる。
Sub 手入力では不可能な入力規則の設定()
Dim a as string
a = String(1000, "a") '"a" * 1000
With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, _
Formula1:=a '255文字を超えるがエラーとはならない
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
上記コードで設定後は正しく動作するが、ブックを閉じて再度開くを修復が始まる。その際、入力規則だけではなく対象シートの書式・図形などがすべて削除される。
一度閉じるまで気づかないため非常に悪質。
対処方法
指定文字列を任意のセルへ書き出し、そのアドレスで指定する。
With Range("A1").Validation
'~~~
'.Add Formula1:=Join(String(1000,"a"),String(1000,"b")) 'だめ
.Add Formula1:="=$B$1:$B$2" '事前にセルへ値を格納しておく
'~~~
言語によっては文字を数値に変換する際に想定外の数値を返す
ExcelVBAというよりは多言語対応時の問題。
言語・地域などによってはコンマとピリオドを逆に設定している場合がある。
CLngなどの文字列を数値へ変換する関数を使用する際には、あくまでシステムの表示形式を参照するため、
"123.45"は"123,45"と解釈される。
'コンマとピリオドを逆に設定した場合
?CLng("123.45")
12345
?CLng("123,45")
123
?CLng(Application.version)
160 ' ← 16.0は160と解釈される
[関連]上記のケース(小数点を入れ替えた場合)ではname.RefersToRangeがエラーとなる。
'名前付き範囲test1,test2を仮定
?Application.names("test1").refersTo
=Sheet1!$A$1
?Application.names("test2").refersTo
=Sheet1!$A$1,Sheet1!$C$1
?Application.names("test1").RefersToRange.Address
$A$1
?Application.names("test2").RefersToRange.Address'複数範囲を含む場合にエラーとなる!!
データラベルの書式を「シートとリンクする」としても標準の書式で表示される
グラフの参照先に揮発性関数(INDIRECTなど)を含む場合、再計算(Application.Calculate)を実行すると、表示が崩れてしまう。
Application.CalculateFullを用いることで対応するが、数式が多い場合は注意が必要。
再計算が手動でないなら任意のセルを更新することでも対応可能。(Range("A1").value = Range("A1").value)
[作業グループ]となっているブックが存在するとき、再計算時にクラッシュする。
開いた別ブックが、作業グループであり,アクティブシートにセル結合を含む時、呼び出し元で再計算処理を行うとクラッシュする。
365では修正済み?
Sub Macro1()
With Workbooks.Add '別ブックを開く(以降、こちらがアクティブ)
.Worksheets(1).Range("B2:C3").Merge 'アクティブシートにはセル結合が存在
.Sheets.Add After:=.ActiveSheet '複数シートを選択(作業グループ)
.Worksheets(Array(.Worksheets(1).Name, .Worksheets(2).Name)).Select
End With
ThisWorkbook.ActiveSheet.Calculate '呼び出し元で再計算
'クラッシュ!
End Sub
対処方法
作業グループを解除(任意のシートを選択)する。
ActiveWindow.ActiveSheet.select
Range.Replaceの結果がタイミングによって異なる。
非表示のシートに対してRange.Replaceを実行すると、同じデータでも結果が異なる場合がある。
Sub 置換が行われたり行われなかったり...
With Worksheets.Add
.Cells(3, 1).Value = "aa"
.Visible = False
.Columns(1).Replace What:="aa", Replacement:="XX" '実行されない場合もある。
.Visible = True
End With
End Sub
これは「検索と置換」画面での検索場所を「シート」と「ブック」のいずれに設定したかによって挙動が異なる。
「ブック」の場合、非表示シートは置換処理の対象外となる様子。
検索場所はRange.Replaceの実行時に引数で指定できないため、ユーザーが置換の際に設定した値が使用される。そのため、Range.Replaceは使用しないほうが無難。
シートのコピー時に「パス名が無効です:XXXXXX.tmp」と表示される。
カレントディレクトリ(curdir)に表示できない文字(日本語環境ならSJISではない文字)が存在する場合に発生。
シートコピーの際に作成されるtmpファイルがうまく参照できていないっぽい?
'SJISでは表示できないフォルダを用意しておく。
'ファイルの指定などを行うとカレントディレクトリが変更される。
debug.print Application.GetOpenFileName
' "C:\😋😜😻😆😶😡😴😹\text.txt"など
worksheets(1).Copy '←ERROR!
365では修正済み?
対処方法
コピー処理の前後でカレントディレクトリを変更しておく。
ただしcurdir,chdirなどはそもそもUNICODEに対応できていないため、使用を避ける。
'カレントディレクトリの変更はchdirではなく、Win32APIを呼び出す。
#If VBA7 And Win64 Then
Declare PtrSafe Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryW" (ByVal CurrentDir As String) As LongPtr
#Else 'さすがにもういらないとは思うけど
Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryW" (ByVal CurrentDir As String) As Long
#End If
'---
chdir fso.getSpecialFolder(2).path 'とりあえず一時フォルダを指定。ここは確実にセーフ?
worksheets(1).Copy
SetCurrentDirectory thisworkbook.path '元の場所に戻すならGetCurrentDirectoryを用いる?
終わりに
適宜追加していきます。