ネムリヤモリ

ツイッターの鍵の向こうに置いておきたいものを書きます

Excel・Access・Word、VBAの困った・調べたをまとめたメモ

【スポンサーリンク】


f:id:teali_s:20220114100246p:plain


1つの記事にしにくい細かいネタをまとめた記事です。
定期的に追加していきます。


Excel

画像を貼り付けたシートを他のブックにコピーすると印刷時だけサイズが変化する

『サイズ変更を行った画像が存在するシート』を他のブックにコピーした時、コピー先で発生。(その時はシートをコピーして新規ブック作成だったはず)
図のサイズの数値はコピー元と変わっていないのに、印刷プレビューを比較すると明らかに違う大きさになっている。
図のリセットをして再度同じサイズに変更してもやっぱり同じ大きさにならず、シートを元のブックに戻しても変化した画像が元に戻ることはない。

解決

画像のプロパティの「セルに合わせて移動やサイズ変更をしない」を選択することで元のブックと同じサイズになる。
(元々の設定が「セルに合わせて移動するがサイズ変更はしない」だからサイズ変わるのがおかしい気がするけど…)

Access

正常に動いているサブフォームのRecordSourceを変数に入れて使うとエラーが起きる

Dim sSQL As String
sSQL = Me.サブフォーム.Form.RecordSource & "WHERE ~ ORDER ~"

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic


※実際はsSQLに代入する処理でサブフォーム側のORDERをReplaceしているが省略

フォームのレコードソースにWHEREとORDERをくっつけてSQL文を作りString型変数に入れ、それをRecordset.Openに使うとエラー。
変数の中身(SQL文)をイミディエイトウィンドウに表示すると途中で不自然な改行がされている。(テーブル名の間で改行されている、とか。手動では絶対やらない)
変数の中身をコピーして改行を消し、クエリを新規作成→SQLビューに貼り付け→デザインビューに切り替えると正しくレコードが表示される。
元ネタのフォームのレコードソースには変な改行がなく正常に動作している。

原因

行が長すぎます | Microsoft Docs
レコードソースの文字数がVisual Basic Editorの1行の文字数の限界を超えているため、勝手に改行されて処理がうまくできなかったらしい。
上記リンクでは1023文字と書いてあるが、当該SQL文にLen関数を使ったら990文字だった。
ちなみにイミディエイトウィンドウも最大文字数が同じでLenを挟み込む余地がなかったため、Excelに行をコピーして調べた。
仕様追加でフィールドが増えてレコードソースの文字数も増え、エラーが起きるようになったと思われる。

解決

フォームのレコードソースをSQLビューで開いてSQL文をコピーし、下記のように適当なところで区切りながら変数に入れた。

"SELECT 1行目--" & _
"2行目---------" & _
"3行目---------"


レポートのデザインを変更しても保存できない

レポートをデザインビューにして変更をかけ、閉じる時の保存確認で「はい」を選択してもレポートが閉じない。保存もされない。
かといって保存せず閉じたら当然保存はされない。

解決

フォームの変更が保存出来ない - Microsoft コミュニティ
「Access レポート 保存 閉じない」でググって先頭に出てきた上記リンクにある解決策、「レポートのコード保持プロパティを『いいえ』にする」を実行した。

フォーム一覧に存在せずコードも見れずエラーを吐き続けるフォームを消す

(解決してそこそこ経っているのでうろ覚えです)

・プロジェクトエクスプローラーには存在する
・フォーム一覧には出てこない
・コードを見ることはできない(開けない?)
・どこかのオブジェクトから呼び出され(?)、エラーを吐き続ける
というフォームがあった。
当時の自分はTwitterに『亡霊的なフォーム』と書いていたので、以下『亡霊』とする。

解決

新規フォームを作成し、亡霊と同名で保存するとコードを開けるようになった。
コードから呼び出し元を特定して亡霊の繋がりを断ち、亡霊のコード保持を「いいえ」にしてフォームを削除することで成仏。

コントロールに間違った値を入力した時のデフォルトメッセージを変更

・定型入力が設定されているコントロールの「入力した値は、このフィールドに設定されている定型入力 '○○' に従っていません。」
・書式などで入力できる型が決まっているコントロールの「このフィールドに入力した値が正しくありません。たとえば、数値型のフィールドに文字列を入力しました。」
前者は「○○」に定型入力の定義、後者は日付型でも「たとえば、数値型の~」と表示され、システム管理者はともかく利用者にはわかりにくいメッセージになっているので変更したい。

解決

「入力した値に関するエラーメッセージ」はコントロールではなくフォームの処理なので、独自のメッセージにしたい場合はForm_Errorで処理する。

Private Sub Form_Error(DataErr As Integer, Response As Integer)   
    '「ActiveControl」で直前に編集していたコントロールの名前を取得して処理を変更
    If Me.ActiveControl.Name = "TeB_Date1" Or Me.ActiveControl.Name = "TeB_Date2" Then
        '日付:テキストボックス 手入力のみ
        MsgBox "日付を正しく入力してください。", vbExclamation, "エラー"
        Me.ActiveControl.Undo 'コントロールを入力前の状態に戻す

    ElseIf Me.ActiveControl.Name = "CoB_Time1" Or Me.ActiveControl.Name = "CoB_Time2" Then
        '時間:コンボボックス 一覧から選択でき、入力もできるようになっている
        MsgBox "時間は一覧から選択するか、0:00~23:59の間で入力してください。", vbExclamation, "エラー"
        Me.ActiveControl.Undo

    Else
        '日付と時間以外のエラー処理
        '(実際はエラーログへの書き込み処理をしているがここでは省略)
    End If
    
    Response = 0 'システム側のメッセージを表示しない
End Sub


Accessのエラーメッセージを取得

Accessで作成したシステムにエラーログを取得する処理を入れているが、Accessのエラーメッセージ(「このフィールドに入力した値が正しくありません。たとえば、数値型のフィールドに文字列を入力しました。」等)はErrで取得できず、全て「アプリケーション定義またはオブジェクト定義のエラーです。」になってしまい、ログがわかりにくい。

解決

ErrではなくAccessErrorを使用する。

Application.AccessError メソッド (Access) | Microsoft Docs

以下は新規Excelブックを作成し、エラーの番号と内容をリストとして書き出す処理。

AccessError

Accessの標準モジュールに貼り付けて実行する。
Errと違い、Raiseでエラーを起こす必要はない。Application.AccessError(番号)でメッセージを取得する。

Sub AccessErrorList()
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    Dim bklst As Object: Set bklst = xlApp.Workbooks.Add
    Dim lp As Long
    xlApp.Visible = True
    xlApp.ScreenUpdating = False

    With bklst.Sheets("Sheet1")
        For lp = 1 To 32683
            .Range("A" & lp) = lp
            .Range("B" & lp) = Application.AccessError(lp)
        Next
    End With
    
    xlApp.ScreenUpdating = True
End Sub


Err
Sub ErrList()
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    Dim bklst As Object: Set bklst = xlApp.Workbooks.Add
    Dim lp As Long
    xlApp.Visible = True
    xlApp.ScreenUpdating = False
    
    With bklst.Sheets("Sheet1")
        On Error Resume Next
        For lp = 1 To 746
            Err.Raise lp
            .Range("A" & lp) = Err.Number
            .Range("B" & lp) = Err.Description
        Next
        On Error GoTo 0
    End With

    xlApp.ScreenUpdating = True
End Sub


Word

Wordテンプレートファイルをテンプレートとして開く

どう頑張ってもテンプレートファイルとしてではなく通常のファイルとして開いてしまう。

解決

Documents.Add メソッド (Word)
VBAでWordテンプレート(dotx)をテンプレートとして開く場合はOpenではなくAddを使う。

Openは「既存のファイルを開く」
Add「既存のファイルを利用して新しいWordファイルを作成」
という感じ。

Private Sub CmB_TempOpen_Click()
    Dim doApp As Object: Set doApp = CreateObject("Word.Application")
    doApp.Visible = True
    doApp.Documents.Add "テンプレを置いている場所のフルパス\テンプレ.dotx"
    Set doApp = Nothing
End Sub


(そりゃ「Access Wordテンプレート 開く」とか「Access VBA dotx Open」で調べても出てこないわけだ…OpenじゃなくてAddだったとは…)

VBA

Microsoft Office 16.0 Object Libraryへの参照設定ができない

「参照可能なライブラリファイル」の一覧に「Microsoft Office 16.0 Object Library」が存在せず、修復しても出てこない。
(会社のパソコンなので再インストールは試していない)

解決

昔誰かが作ったシステムの参照設定を見たらObject Libraryへの参照があり、途中までのパスが確認できたので、それを基にフォルダを潜って「MSO.DLL」を選択し、Accessを再起動したら無事に参照設定できた。

そのパスはこちら↓
C:\Program Files (x86)\Microsoft Office\root\vfs\ProgramFilesCommonX86\Microsoft Shared\OFFICE16

(これ何に使うんだっけ?と思って調べたらファイル選択ダイアログ(Application.FileDialog)を使うためのものだった)

【スポンサーリンク】