タグ: VBA

Accessで住所支援入力というのがあるが、プロパティで設定せずにロジックで行うにはどうしたらよいかという問題への対応策。
使うのは住所支援入力で使っているライブラリと同じもの(だとは思う)。MsYubin7.dll。

３つのテキストボックスPrefText、CityText、TownTextに、都道府県、市区町村、町域をそれぞれ設定する前提で。

まず呼出元。

Dim res() As String
ConvZip2arrAddr("1600005",res)
PrefText = res(0)
CityText = res(1)
TownText = res(2)

まず呼出先。これは別モジュールを作成して記載しておけばいい。

Private Declare PtrSafe Function zcGetZipDecision Lib "MSYubin7.dll" Alias "GetZipDecision" _
                                                                            (ByVal ZipCode As String, _
                                                                            ByVal szKen As String, _
                                                                            ByVal szCty1 As String, _
                                                                            ByVal szCty2 As String, _
                                                                            ByVal szTwn As String, _
                                                                            ByVal szTwnExt As String) As Long

Public Sub ConvZip2arrAddr(ByRef zipCd As String, arrAddr() As String)
    
    Dim pref    As String * 40
    Dim city1   As String * 40
    Dim city2   As String * 40
    Dim town1   As String * 40
    Dim town2   As String * 500
    Dim arrRet(4)    As String
    
    On Error GoTo ErrFunc
    
    If zipCd = vbNullString Then Exit Sub
    If Len(zipCd) <> 7 Then Exit Sub

    If Val(zipCd) Then
        zcGetZipDecision zipCd, pref, city1, city2, town1, town2
        arrRet(0) = Left$(pref, InStr(pref, vbNullChar) - 1)
        arrRet(1) = Left$(city1, InStr(city1, vbNullChar) - 1)
        arrRet(2) = Left$(city2, InStr(city2, vbNullChar) - 1)
        arrRet(3) = Left$(town1, InStr(town1, vbNullChar) - 1)
        arrRet(4) = Left$(town2, InStr(town2, vbNullChar) - 1)
        ReDim arrAddr(0 To 2)
        arrAddr(0) = arrRet(0)
        arrAddr(1) = arrRet(1) & arrRet(2)
        arrAddr(2) = arrRet(3) & arrRet(4)
    End If
    Exit Sub
    
ErrFunc:
    Debug.Print "No." & Err.Number & ":" & Err.Description
End Sub

Access2019で試したが、他のバージョンでもそれほど違いはないと思う。

AccessではAutoExecなどという起動時に走らせるマクロがある。
ExcelならばAuto_Openなどというプロシージャで昔は作っていたようだ。（今だとThisWorkbook_Openとかだろうか）

Accessファイルを起動するとき、このマクロを起動させたくなければ、一旦Shiftキーを押しながらファイルを開くのだが、これをVBAで実施したいときどうすればよいのだろうか・・・

Excelファイルを開くときは、

Excel.Application.EnableEvents = False

とすればよいのだが、Access.Applicationにはそのようなプロパティがない。

というので、前にちらっとSendKeyでShiftキーを押して起動しているコードを見たこともあり、同じようなものを作ってみることにした。
ただ、SendKeyはちょっと嫌なのでSendInputというAPIを使った方法にすることにした。（どっちも嫌だけど）

まずはTypeの定義

Private Type KEYBDINPUT
    VK          As Integer
    Scan        As Integer
    Flags       As Long
    Time        As Long
    ExtraInfo   As Long
    Dummy1      As Long
    Dummy2      As Long
End Type

Private Type INPUT_TYPE
    IType           As Long
    KI              As KEYBDINPUT
End Type

WinAPIの定義(64bitの場合PtrSafeをお忘れなく)

Private Declare Function SendInput Lib "user32.dll" (ByVal nInputs As Long, pInputs As INPUT_TYPE, ByVal cbsize As Long) As Long

利用する定数の定義

Private Const VK_SHIFT        As Long = &H10          'Shiftキー
Private Const KEYEVENTF_KEYUP As Integer = &H2        'KeyUp(KeyDownのほうは0)
Private Const KEYEVENTF_EXTENDEDKEY As Integer = &H1  '拡張コード
Private Const INPUT_KEYBOARD As Integer = 1           'KeyboardイベントでSendInputを利用する

ここまでで定義が済んだので、Accessファイル起動処理を書いていく。
SendInputについての情報は以下の通り。

• SendInputはMouseイベントなどでも利用できるので、Keyboardイベントであることを指定する。
• Shiftキーを押した→ファイルを起動した・・・とここまでで終わりではなく、Shiftキーを戻す(KeyUp)の処理まで行わないと、ずっとShiftキーが押された状態のままになってしまう。
• SendInputの最初の引数はイベントの数で今回は1つなので1。例えば、Dキー⇒Oキー⇒Enterキーと押す場合は3。
• SendInputの第2引数は押下するキーの情報(配列)、第3引数は第2引数のデータ長。

    Dim ac As Object
    set ac = CreateObject("Access.Application")
    Dim it(0) As INPUT_TYPE

    'Shiftキー押下
    With it(0)
        .IType = INPUT_KEYBOARD        
        .KI.Vk = VK_SHIFT             
        .KI.Scan = 0
        .KI.Flags = KEYEVENTF_EXTENDEDKEY Or 0                'DOWN
        .KI.Time = 0
        .KI.ExtraInfo = 0
    End With
    SendInput 1, it(0), Len(it(0))
    
    'Accessファイルを開く
    ac.OpenCurrentDatabase {起動するファイル名}, True, {パスワード}
        
    'Shiftキー押下の戻し
    it(0).KI.Flags = KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP   'UP
    SendInput 1, it(0), Len(it(0))

最終的にAccessを閉じて処理終了

    ac.Quit acQuitSaveNone

フラグを渡すのに「KEYEVENTF_EXTENDEDKEY Or 」というのをつけないと、反映されなかった。
ここに手間取り結構時間がかかったのと、あとはとにかく「こんなことやりたいねん！」って検索しても出てこない、っていうところで時間がかかった。
VBA Access Autoexec 無効 とかキーにしても、Shiftキー押しながら起動すればいいよ！としか出なくて腹立つぅ～～～!!
って思ってしまった。検索能力ってIT技術の１つだなぁ～とつくづく実感。

参考URL
VBレスキュー(花ちゃん) 『３．SendInput 関数を使ってプログラム上からキーボードを操作する(12_Key_03)』

Microsoft Windowアプリ開発『仮想キー コード』

SQLを実行してきてデータを取得するまではよいのだが、その後シート上に出力するときに異様に時間がかかる場合がある。
で、端末個体の理由は置いといて、複数のワークブックを開けていた場合に起こるのであれば、対策は、

　処理中は必要なとき以外「自動計算」させない！

ということらしい。

With Application
    .ScreenUpdating = False
    .Calculation =xlCalculationManual
    .EnableEvents = False
End With

としておけば、
　画面を更新せず、
　再計算させず、
　イベント有効にしない
とできるわけである。
これで、余計なイベントも発生しないので良いのだが、再計算してほしいときにはCaluculateメソッドを発行しておかねばならない。

で、Application.Calculation = xlCalculationManualをせず、シート上にバサッと出力したいなぁ～などと思っていたら、

　WorksheetFunction.Transpose()

シート関数のTRANSPORT関数を使って、引数に出力したい配列を設定することで、ドバっとあっという間に出力できてしまうやり方を紹介しているページがあったので、ありがたやありがたやとやってみたところ、本当にあっさりとドバっと出力された。

参考URL：エクセルの神髄「マクロVBAの高速化・速度対策の具体的手順と検証」

入れる配列は（横、縦）の状態にして、Transport関数で縦横逆にして出力！ってなことになっている。
ありがたやありがたや！

ワークシート上にある「SHP1」という名前の図形をコピーして、「SUB_SHP1」という名前を付け、B2セル内に配置するロジックを考えてみた。
コピー元の図形のDuplicateメソッドをつかって、新しい図形(コピー先:以下サンプルでは変数「shp」のこと)を作り、その新しい図形のプロパティをいじって、図形の位置とか、図形の名前、図形に紐づくマクロの設定などを行う。
サンプルでは、セルB2の下部中央に配置する方法。

Dim ws  As Worksheet
Dim shp As Shape

Set ws = Thisworkbook.Worksheets("Sheet1")
Set shp = ws.Shapes("SHP1").Duplicate
With shp
  .Top = ws.Range("B2").Top - .Height -5
  .Left = ws.Range("B2").Left + (ws.Range("B2").Width - .Width) / 2
  .Name = "SUB_SHP1"
  .Visible = msoTrue
  .OnAction = "RunXXX"
End With
Set shp = Nothing
Set ws = Nothing

図形のコピーに「Duplicate」を使うというのは初めて知った。duplicateの意味が「複写する」ってことらしい。

Blogの外観をちょいと変えてみた。また変えるかも。

さて、Windows10でExcel2010とか2013とかを使うようになって、Windows7で作ったExcelマクロのActiveXコントロール（コンボボックス）がうまく表示されないようになった。

これは困った・・・

グループ化すれば治る・・・って、治んない。

困りましたな・・・

ってことで、もうフォームコントロールに変えることにした。
面倒だわ・・・
ActiveXコントロールだと、リストの情報はマクロ上で設定すればいいのだが、フォームコントロールはリストの情報をセルに持たせて、そのセルとリンクを貼る・・・という厄介な手順を踏まなければならない。

ということで、ActiveSheet上にあるフォームコントロール「DLIST」のリストにしたい情報が、同じシートのセル「A2～A10」にあるとすると、

With ActiveSheet.Shapes("DLIST").ControlFormat
    .ListFillRange = "A2:A10"
    .LinkedCell = "B2"
End With

参照リストセルが別のシート(仮に「Sheet2」とする)にある場合、ListFillRange の設定をちょっと変える必要がある。

    .ListFillRange = "Sheet2!A2:A10"

でも、コントロールのフォントサイズの変え方がわかんない・・・
もうヤダ。

Shell関数を使って別のexeを起動。

できんかった・・・・・・

なので調べた。

どうやら、カレントディレクトリをexeのある場所に指定しないとダメな様子。
で、ChDir関数を使ってカレントディレクトリの変更！ってやってみたけど、うまくできなかったので、別の方法でカレントディレクトリを変える。

以下は、Workbookと同じフォルダにあるtest.exeを実行してみるサンプル。
起動できたら、Excelを最小化させる

    Dim p   As String
    Dim res As Integer
    
    On Error Resume Next
    p = ThisWorkbook.Path
    If Right(p, 1) <> "\" Then p = p & "\"
    With CreateObject("WScript.Shell")
        .CurrentDirectory = p    // カレントディレクトリを変える
    End With
    res = Shell("test.exe", vbNormalFocus)
    If res <> 0 Then Application.WindowState = xlMinimized

Windowsのログインユーザアカウントをとってきたい！ってのを調べてロジックに組み込んだはいいが、このブログ上にメモしてなかった。

まぁWindowsにログインするときに入力するアカウントのことなんだけど、環境変数関係の情報を取得するときは、Environ関数を使う。

Environ(“USERNAME”)

と、こんな感じ。

引数にどんなものがあるか、どんな戻り値になるか、デバッグして調べてみるときは、こんな風に。

Debug.Print "OS：" & Environ("OS")
Debug.Print "デフォルトドライブ：" & Environ("HOMEDRIVE")
Debug.Print "デフォルトユーザパス(ドライブなし)：" & Environ("HOMEPATH")
Debug.Print "tempフォルダパス：" & Environ("TEMP")
Debug.Print "ログインユーザID：" & Environ("USERNAME")
Debug.Print "systemフォルダパス：" & Environ("WINDIR")

基本的にScripting.Dictionaryは、

Dim dic     As Object

Set dic = CreateObject("Scripting.Dictionary")
If Not dic.Exists(a) Then
  dic.Add a,b
End If

みたいに、Existsメソッドを利用することが多いのだが、
やはり１つずつ確認して処理を行うこともなくはない。

Dim i As Long
For i = 0 To dic.Count - 1 Step 1
    debug.print dic.Keys()(i) & "-" & dic.Item(i)
Next i

もしくは、

Dim v As Variant
For Each v In dic.Keys()
    debug.print CStr(v) & "-" & dic(v)
Next

で、いずれも、Keys() と両括弧を付けるところがポイント。

色々サイトを見てみたのだが、この()が抜けていたり、ItemがItemsになっていたりして、混乱している記載が多く、ここまでたどり着くのに英語のサイトまで見に行ってしまった。

非常にやっかいだが、もともとこういう使い方するようなオブジェクトではないのだろうから、仕方ない。

ちなみに削除するときはRemoveを使う。（Deleteではない）

Dim v As Variant
For Each v In dic.Keys()
    dic.Remove v
Next

ファイル渡すからプログラム組んでくれ、っていわれてプログラム作成している最中、
ファイルも寄越さないうちから、「進捗どうですか？」って聞かれて、ちょいムカっとしたのは今週の始め。
んで、ムカついたからもう少し待ってねメールのついでに、送ってきた中途半端な仕様書の「重箱の隅にもならない場所」(ちゃんと書いて！ってな場所）をつつくような質問を畳みかけてやった。（フン！）

まぁ、別に大して怒っている訳ではないのだが、
　「自分がやるべきこともしないで、なんなんだ？」とか、
　「いやいや○日かかるって言ってるでしょ！何日目だよ今日！」とか・・・
色々思うところはあった。
そうは言いつつ、自分もこうならないように気を付けようと、ちょいと思ったのだった。

で、慣れないDoCmdに苦戦しつつ、ExcelファイルをReadOnlyで開いて、データを読込み、終わったら閉じる処理を書いてみた。
DisplayAlertsをFalseにしているのは、Excelで発生したエラーやら確認メッセージを出させないためだけで、値をとってくるだけなら特に必要はないと思う。

Dim xls As Object
Dim wb  As Object

Set xls = CreateObject("Excel.Application")
xls.DisplayAlerts = False
Set wb = xls.Workbooks.Open(FileName:=[Excelパス], ReadOnly:=True)

 ・・・（読込処理）・・・

wb.Close SaveChanges:=False
Set wb = Nothing 
xls.DisplayAlerts = True
xls.Quit
Set xls = Nothing

面倒だなぁ・・・Excelでいいじゃん？
だって、xlUpとかxlToLeftとかのExcelVBA特有の定数も使えないし。
（参照設定で設定すれば、使えるようにはなるけどサ）

とは思ったのだが、まぁ、後々Accessの方がいいこともあるかもって思ったので、お客さんの言うとおりAccessにした。
だけど、やはり、今回は、Accessにデータを取込むわけでもなく、別DBに出力するロジックだったので、尚更、AccessでExcelを開くというのは、かなり馬鹿馬鹿しく感じた。
もちろん、できるならImportしたいところだが、できるほど単純なデータではなかったので、わざわざロジックを組む。

　　・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

で、今回は読み込んだデータをAccessテーブルには放り込まなかったのだが、Accessに取込むとなるとどうするのか・・・ということも考えてみた。

しっかし、AccessのTableってのは、INSERT文で出力しようが、AddNew・Updateで入れてみようが、まぁ、遅いのなんの・・・
さらに複数のSQL文を一括実行する方法なんて探してみたのだが、

Accessにはない！

ということなので、１つ思いついたのが、
　・Excelデータを取込んで、テーブルImport用のCSVを作成。
　・CSVをImportする。
という方法。
確かに、INSERTを発行するより速いのだが、かといって、すこぶる速いという訳でもなかった。件数が多ければ、明らかに速くなる気はする。

しかし、ここで問題発生。文字コードだ。

UTF-8文字が入力されたExcelデータであったため、ADO.Streamを利用して、BOM無しのUTF8でCSV出力。
かつ、インポートするときも

　DoCmd.TransferText acImportDelim, , temp_table , csv_path, True, , 65001 　　

というように、文字コード(65001がUTF-8を示す)を指定して取込む必要があった。

ということで、いろいろ試しにやったのだが、プログラム自体はすんなり作成できた。

ってか、まだファイル来ないし・・・

略すとPPAPみたいになってるけど、ExcelのVBAプロジェクトのパスワードがわからないとき（忘れたとき）は、それを外すやり方がある。

何だっけ・・・

と思って調べた。

ここでは詳細は書かないけど、検索ワード的には
　Excel, VBA, DPB=
といったところか。

DPB=のダブルクォーテーションで括られた中身が暗号化?されたPWで、それをうまいこと置換えるわけなんだけど、バイト数が合わないとVBA関連のファイルが潰れちゃうので注意しなくてはならない。
なので、バイト数のきっちり合った置換文字を作成するため、新規ExcelファイルにVBAパスを付けて保存した後バイナリファイルの中身を確認する。
（パスワードの桁数からDPBの中身の桁数が容易にわかるわけではないので、色々作って合致した！ってなパスワードが見つかってから置換えるわけ。ちょいと面倒なのだが、熱さ過ぎればチョメチョメチョメと。）

あらら、結構書いてしまったぞい。
忘れたときのためなので、悪用するべからず！

Dir関数でファイルがないってときに、
　ただファイルがないのか、ネットワークにつながってないのかわかんないの？
　Ping飛ばしてネットワークにつながってるのか調べたらいいじゃない？
みたいな要件があって、まぁ、正直、「そこまでやるか？」って思ったけど、そういう環境で動いている人は、そういう発想になるんだろうなぁ・・・と思って、教えてもらったPing送信術をアレンジしてExcelに埋め込んでみた。

Public Function CheckNetwork(ip As String) As Boolean
    
  Dim wsh As Object
  Dim buf As String
    
  On Error GoTo ErrCheck

  Set wsh = CreateObject("WScript.Shell")
  wsh.Run "%ComSpec% /c ping -n 1 " & ip & " | clip ", 0, True
  buf = GetObject("\" _
               , "htmlfile").ParentWindow.ClipboardData.GetData("text")
  If InStr(buf, "ラウンド トリップの概算時間") > 0 Then
      CheckNetwork = True
  End If
  Exit Function
ErrCheck:
  err.clear
End Function

ちなみに、「%ComSpec%」は、「%SystemRoot%\system32\cmd.exe」のことらしい。
「 | clip」でクリップボードに入れて、それをbufに読み込むようにした。
また、Shellは、Execで動かす方法とRunで動かす方法があるが、Runのほうが、コマンドの窓が表示されないようにできるので、こちらを採用。
面倒な要件は、解決できると嬉しい。
解決しないと、逆恨みしそうだけど。

あるよねぇ～～～～
ってことで、

Sub ProtectSheet
    With ws
        If Not .ProtectContents Then         'ロックされていないときだけロック処理
            .Protect Password:="password"
                   , DrawingObjects:=True
                   , Contents:=True
                   , Scenarios:=True _
                   , AllowFiltering:=True     'ここでフィルターOKにする
        End If
    End With
End Sub

以下は試していないけれど、これでもOKらしい。

Sub ProtectSheet2
    With ws
        If Not .ProtectContents Then         'ロックされていないときだけロック処理
            .Protect Password:="password"
                   , DrawingObjects:=True
                   , Contents:=True
                   , Scenarios:=True _
                   , userInterfaceOnly:=True
        End If
        .EnableAutoFilter = True
    End With
End Sub

セル選択＆移動させる方法はいろいろあるけど、一番手っ取り早いのはApplication.Gotoのようだ。

Sub ActivateRange(rng as Range)
    On Error Resume Next
    Application.Goto rng, True
    err.clear
End Sub

１つ目の引数は選択セル、２つ目の引数はスクロールして移動するかどうか、らしい。
選択だけだったら、rng.selectでもいいように思うけど。

これを使わずに選択させようとなると、

Sub ActivateRange(rng as Range)
    On Error Resume Next
    rng.parent.parent.Activate
    rng.parent.Activate
    rng.select
    err.clear
End Sub

みたいなかんじで、ブック→シート→セルをアクティブにしないといけないわけで、これは面倒。
（親がアクティブになっていないに、子をアクティブにはできないのだ）
楽にできる関数があるということで、メモ。

このパスはローカルなのかネットワークサーバなのか・・・
とりあえず判定ロジックを作ってみた。

'ローカルならTrue、ネットワークならFalse
Public Function CheckLocal(filepath as String) As Boolean
    
    Dim cap As String
    Dim fso As Object
    Dim obj As Object
    
    '先頭が\なら確実にネットワーク
    cap = Left(filepath, 1)
    If cap = "\" Then
        Exit Function
    End If

    'ドライブ割当のローカル/サーバ確認
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each obj In fso.Drives
        If obj.DriveLetter = cap Then
            If obj.DriveType = 3 Then
            Else
                CheckLocal = True
            End If
            Exit Function
         End If
    Next
    'ここまで来たらどこかは不明
End Function

久方ぶりの投稿。いろいろあったけど、それはまた別に書くとして、CSVファイルを読み込むときの高速なやり方を探してたらQueryTablesを使った方法というのがあったので、やってみた。

    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks.Add
    Set ws = wb.Worksheets(1)
    With ws.QueryTables.Add(Connection:="text;mega.csv", Destination:=ws.Range("A1"))
        .Name = "megacsv"
        .TextFileCommaDelimiter = True
        .Refresh
    End With

すごかった。30万行一瞬。

印刷範囲指定をしてウィンドウ枠を固定すると、アクティブセルのある領域以外の図形がちらついて見える事象。
Excel2010でしか起きないらしい当該事象だが、さてどうしようかと考えると、結局は印刷範囲指定をやめる結論に至った。
解決作的には根治してもらわなければならないのだが、MSさんが重い腰をあげないので、
自分で腰を上げるとするなら、例えば、印刷ボタンを作って、ボタンを押したときだけ印刷範囲指定→印刷→印刷範囲クリアすればよいのかなぁと考えた。
でも、厳密に印刷設定を行う必要性がないのであれば、印刷の拡大率を設定しておくだけでも何とかなるような気もする。

忘れる前にメモっとく。
VBAからコマンド実行を非同期で行う時に使うShell関数。
第2引数にvbMinimizedNoFocusを設定すると、コマンドプロンプトは非表示になって、裏側で走る仕組みにできるそうな。

Dim cmd_buf As String
Dim res     As Double

cmd_buf = "cmd /c sqlcmd -S {DB-Source} -U {DB-UserId} -P {DB-UserPw} -d {DB-Name} " _
         & " -Q ""EXEC {プロシージャ名} {*}, '{*}' """ 
res = Shell(cmd_buf, vbMinimizedNoFocus)

{}書きは環境に合わせて変更
{*}はストアドの引数で、文字列の場合はシングルクォーテーションで囲む。

Shellの戻り値はタスクIDで、「0」がかえってきたら動いていないということらしい。
非同期なので勝手に動いて、勝手に終わる。

終わったらウィンドウをアクティブにするとかすると、終わったことがわかるらしい。

If res > 0 Then Call AppActivate(res)

と、こんな感じかね。
ふむふむ、しかしだね、裏側で勝手に動いているわけで、下手にPCシャットダウンしちゃうと、処理が途中で終わっちゃう！ってところが怖い。

で、次。
リモート環境にあるバッチファイルを実行する方法。

'server：リモートサーバ(user_id/user_pwでログイン)
'exe_name：実行ファイル, exe_position：実行ファイルのパス
Public Function ExecuteRemoteExe(server As String, user_id As String, user_pw As String _
                                , exe_name As String, exe_position As String) As Boolean

    Const AUTHENTICATION_LEVEL_PKT_PRIVACY = 6
    
    Dim obj_locator As Object
    Dim obj_server  As Object
    Dim obj_process As Object
    Dim res As Long
    Dim pid As Long
    
    On Error GoTo ErrExecute
    
    Set obj_locator = CreateObject("WbemScripting.SWbemLocator")
    Set obj_server = obj_locator.ConnectServer(server, "root\cimv2", user_id, user_pw)
    obj_server.Security_.authenticationLevel = AUTHENTICATION_LEVEL_PKT_PRIVACY
    
    Set obj_process = server.get("Win32_Process")
    res = obj_process.Create(exe_position, Null, Null, pid)

    Select Case res
        Case 0
            ExecuteRemoteExe = True
        Case 2
            Msgbox "アクセスできませんでした。"
        Case 9
            Msgbox "パスが正しくありません。" 
        Case 21
            Msgbox "パラメータが正しくありません。"
        Case Else
            Msgbox "バッチを実行できませんでした。"  
    End Select
        
ErrExecute:
    
    Set obj_process = Nothing
    Set obj_server = Nothing
    Set obj_locator = Nothing
    
End Function

これでリモートのバッチファイルを実行することができる。
できるんだけど、これもこっち側のPCを切ってしまうと、バッチも終わってしまう・・・
なんとかならんか。

モノを整理するのにモノを買う・・・
という、断捨離しているはずなのにモノを増やす、愚かしいことをやってしまった。

いや、何にせよ、今のままじゃイカン！と、虚無感たっぷりに自分に活を入れつつ。

ExcelのRangeオブジェクトのメソッドにあるResize。起点セルと行列数を指定して範囲指定するのに使っているようだ。
こんなメソッドがあるとは全く知らなかったが、ネットサーフィンしてたらたまたま見つけたので試してみる。

参照サイト：Range,Cells」と「Resize」のセル範囲指定を比べてみる

パターン1：ごくごく一般的な範囲指定

With ThisWorkbook.Worksheets(1)
    .Range("C2:E4").Select
End With

パターン2：一般的なはずな範囲指定

With ThisWorkbook.Worksheets(1)
    .Range(.Cells(2, 3), .Cells(4, 5)).Select
End With

パターン3：「Resize使って同じことしてみるよ」な範囲指定

With ThisWorkbook.Worksheets(1)
    .Cells(2,3).Resize(3,3).Select
End With

パターン4：「Offset使って同じことしてみるよ」な範囲指定

With ThisWorkbook.Worksheets(1)
    .Range(.Cells(2,3),.Cells(2,3).Offset(3,3)).Select
End With

パターン1から4まで、すべて同じ範囲が指定される。（つまり、セルC2～E4）
なるほど、少なくともパターン4よりは、パターン3の方がきれいだ。
パターン2と3、どちらを使うかは、周辺のロジック次第。

なお、参照サイトには「Rangeが参照しているシートがアクティブになっていないとエラーになる」と書いてあるが、実はどのメソッドでも同じ。Selectメソッド自体、アクティブシート上にでないと動かないので、ResizeやOffsetとは関係がないと思う。

シートを保護する、解除するというのは、

Worksheets(“Sheet1”).Protect
Worksheets(“Sheet1”).Unprotect

という、メンバー（サブプロシージャ）でできるのだが、Protectするときの引数に、「UserInterfaceOnly」というのがあり、これをTrueにしておけば、シート保護の解除をしなくても、マクロでシート上のデータを変えられるそうな。

Worksheets("Sheet1").Protect UserInterfaceOnly:=True

なので、いままでわざわざシート保護を解除して処理をしていた自分が情けなくなった。
（何年VBA使ってんだよ！と自分を責めてみる。）

まだまだ知らないことがあるなぁ。

この引数を指定しない場合は、ちゃんと保護解除をしてから触ること。

Optionalで指定した引数は省略可能だが、設定されて呼び出されているかどうかを確認する関数がIsMissingだそう。
しかしながら、この変数の型がVarient型のときにしか機能しないというお粗末もの。

Private Sub Test (Optional a As Varient)
    If Not IsMissing(a) Then
        '設定されているとき
    Else 
        '設定されていないときのロジック
    End If
End Sub

下のようにLong型にすると、a＝0と判断されてしまうので×。

Private Sub Test (Optional a As Long)
    If Not IsMissing(a) Then
        '全部こちらに入ってしまう
    Else 
        'こっちに来ない
    End If
End Sub

ということで、省略時の初期値を設定しておき、条件分岐をする。

Private Sub Test (Optional a As Long = -1)
    If a>0 Then
        '省略されていないとき
    Else 
        '省略されたとき
    End If
End Sub