エクセル VBA 高速化

いろいろやってみた結果、表示のアップデートをやめるのはすべてにおいて速度効果があります。
マクロでなにかしら更新をする前に
Application.ScreenUpdating = False
としておき、終わったら
Application.ScreenUpdating = True
と戻しましょう。
戻し忘れると、更新できなくなります。
また、忘れてセーブすると次に開いたときに更新ができない状態になります。

Error エクセル VBA ファイル Open/Close ファイルはあるのに開かない

エクセルVBAでファイルをOpenするときに、理由はわかりませんが、失敗することがある様子です。
そこで、開くまで何度か試すようにコードを修正します。

Function FileOpen(FilePath As String, FileName As String, wb As Workbook) As Boolean
    Dim fp As String
    fp = FilePath & "\" & FileName
    FileOpen = True
    If Dir(fp) == "" Then
        MsgBox "ファイルが存在しません。", vbExclamation
        FileOpen = False
        Exit Function
    End If
    On Error Resume Next
ReOpen_00:
    Set wb = Workbooks.Open(FileName:=fp, Notify:=False, ReadOnly:=True)
    ' ここで何度か開きなおしてやる。
    If Err.Number > 0 Then GoTo ReOpen_00
    On Error GoTo 0
End Function

Error エクセル VBA ドロップダウンリスト 実行時エラー'1004'

実行時エラー'1004'
アプリケーション定義、またはオブジェクト定義のエラーです。

エクセルVBAでドロップダウンリストを自動生成しているときに上記エラーが出ました。
これは、エラーの起きたファイルを開いていると、ほかエクセルファイルでも出ます。
解決方法をいろいろ探ってみましたが、何とかできるのですが、決まったやり方がわかりませんでした。
問題のあるエクセルシートを開かずに、新規のエクセルでコードを実行するとエラーは起きませんので、コードに問題があるわけではありません。
面倒くさいですが、新規エクセルブックに全部コピーするのが最も早い回避方法でした。

起きた原因はおそらく、エクセルのオブジェクトの命名規則でバグがあります。
作成しようとするオブジェクトと同じ名前のオブジェクトが完全に消えてなくて、しかも、消すには作成したオブジェクト名で強引に消すという方法しかなく、どこでエラーが起きたか特定するのに非常に時間がかかります。(消したはずのオブジェクトが何かしら残っている感じです。)

ネットではそのオブジェクトをDeleteしてからやればできる。とか、異なるセルをSelectしてからすればできる。などと解決策をかいておりましたが、いずれも駄目でした。

このエラーが起きたら、バックアップから作業を再開というのが最速の手段でした。

エクセル VBA 列数 行数の取得

エクセル VBA で指定行の項目数、指定列の項目数を取得します。

c列の項目数を上から取得します。(空欄で止まります。)

ActiveSheet.Cells(1, c).End(xlDown).Row

c列の項目数を下から取得します。(空欄を含んでいてもOK。)

ActiveSheet.Cells(Rows.Count, c).End(xlUp).Row

r行の項目数を左から取得します。(空欄で止まります。)

ActiveSheet.Cells(r, 1).End(xlToRight).Column

r行の項目数を右から取得します。(空欄を含んでいてもOK。)

ActiveSheet.Cells(r, Columns.Count).End(xlToLeft).Column

エクセル VBA ドロップダウンリスト

リストからドロップダウンリストを作成します。

エクセル VBA 関数を使う

VBAから関数Matchを使う。 たとえば、対象Book(TargetBook)のシート("word")のエリア(A1:A20000)から”てきとう”を検索するにはこのようにする。

Dim r As Variant
    r = Application.WorksheetFunction.Match("てきとう", TargetBook.Sheets("word").Range("A1:A20000"), 0)

返り値 r には 行番号が入っています。
しかしながら、範囲内に検索文字がない場合にError となりますので、対応します。

エクセル VBA ファイル Open/Close

エクセルファイルをOpen/Closeします。

Function ファイル読込(BkName As String, ObjBk As Workbook)
    Application.ScreenUpdating = False
    Set ObjBk = Workbooks.Open(FileName:=BkName, Notify:=False)
'   ObjBk.Windows.Application.Visible = True ' これは使わない。
    Windows(ObjBk.Name).Visible = True
    Application.ScreenUpdating = True
End Function

BkNameにフルパスを含むファイル名を入れて関数を呼びます。
ObjBkにWorkbookのファイルポインタを入れて返します
非表示でOpenするときにはVisibleをFalseにします。
(デフォルトでTrueなのですが、説明のために。) 保存終了にするときはSaveChangesをTrueにします。

エクセル VBA オプション

エクセル VBA の先頭で

Option Explicit

と記述すると、宣言していない変数でErrorとなります。
スペルミス用ですね。

エクセル VBA オブジェクトと変数

なんしか、エクセルを学習します。

オブジェクト

階層構造になっています。

Application→Workbooks→Worksheets→Range

変数

変数にオブジェクトをとり出す。

Sub test_00()
    Dim testBook As Workbook
    Dim testSheet As Worksheet
    Dim testRange As Range
    Set testBook = Workbooks("テスト.xlsm")
    Set testSheet = testBook.Worksheets("Sheet1")
    Set testRange = testSheet.Range("A1")
    testRange.Value = 20
End Sub

直接代入

一つだけなら直接代入します。

Sub test_01()
    Workbooks("テスト.xlsm").Worksheets("Sheet1").Range("A1").Value = 20
End Sub