■ジャグ配列をシートに書き込む

ジャグ配列といっても、単純なケースでの話。



多段階配列、いわゆる配列の配列なのでそのままワークシートに書き込む事はできません。
でも１次元配列の中に格納されている配列が１次元配列か、最初の次元が単一の２次元配列の時、
かつ、中の配列の要素数が一定の場合に限りますが、以下のように書き込みできます。

Sub sample()
　　Dim r As Range
　　Dim i As Long
　　Dim aryX(1)
　　Dim aryY(1)
　　Dim x(2)
　　Dim y(0, 2)

　　For i = 0 To 2
　　　　x(i) = i
　　　　y(0, i) = i
　　Next
　　For i = 0 To 1
　　　　aryX(i) = x
　　　　aryY(i) = y
　　Next

　　Stop
　　Set r = Range("A1").Resize(2, 3)

　　'Transpose関数
　　With WorksheetFunction
　　　　r.Value = .Transpose(.Transpose(aryX))
　　　　r.ClearContents
　　　　r.Value = .Transpose(.Transpose(aryY))
　　End With

　　'FormulaArrayプロパティ
　　r.ClearContents
　　r.FormulaArray = aryX
　　r.ClearContents
　　r.FormulaArray = aryY

　　Set r = Nothing
End Sub

前述条件のような単純な多段階配列の場合、
ワークシートTranspose関数を使って２次元配列に整理し直す事ができます。
また、FormulaArrayプロパティを使う事によって配列のままワークシートに書き込む事ができます。
よく見かけるのはTransposeを２回使う手法ですね。
FormulaArrayプロパティの方は使えない事はないけど、激遅なので使わないほうが良いです。
ジャグ配列というと、DictionaryのItemに配列をセットするパターンにも繋がるので、ちょっとDictionaryを使ってみた計測コード。

Option Explicit

Sub test()
　　Const rn As Long = 1000
　　Const cn As Long = 5
　　Dim dic　As Object
　　Dim i　　As Long
　　Dim j　　As Long
　　Dim n　　As Long
　　Dim tmp(1 To cn)
　　Dim t As Single

　　Set dic = CreateObject("scripting.dictionary")
　　For i = 1 To rn
　　　　For j = 1 To cn
　　　　　　n = n + 1
　　　　　　tmp(j) = n
　　　　Next
　　　　dic(i) = tmp
　　Next

　　t = Timer
　　With Application
　　　　Sheets.Add.Cells(1).Resize(rn, cn).Value = _
　　　　　　　　　　　　　 .Transpose(.Transpose(dic.items))
　　End With
　　Debug.Print "Transpose", Timer - t

　　t = Timer
　　Sheets.Add.Cells(1).Resize(rn, cn).FormulaArray = dic.items
　　Debug.Print "FormulaArray", Timer - t

　　t = Timer
　　Sheets.Add.Cells(1).Resize(rn, cn).Value = AryLoop(dic.items)
　　Debug.Print "AryLoop", Timer - t
End Sub
'---------------------------------------------------------------------
Function AryLoop(Ary)
　　Dim Lx As Long
　　Dim Ly As Long
　　Dim Ux As Long
　　Dim Uy As Long
　　Dim i　As Long
　　Dim j　As Long

　　Ly = LBound(Ary)
　　Uy = UBound(Ary)
　　Lx = LBound(Ary(Ly))
　　Ux = UBound(Ary(Ly))

　　ReDim v(Ly To Uy, Lx To Ux)
　　For i = Ly To Uy
　　　　For j = Lx To Ux
　　　　　　v(i, j) = Ary(i)(j)
　　　　Next
　　Next
　　AryLoop = v
End Function

1,000×10配列で

Transpose      0.046875 
FormulaArray   4.234375 
AryLoop        0.046875 

こんな結果です。[winXPsp2/xl2003sp3]

■Sheet上MSForms.TextBoxイベントのCancel制御

最近勉強させてもらった事ですが、ワークシート上にMSForms.TextBoxを配置して、SheetモジュールにそのChangeやKeyDownなどのイベントプロシージャがある場合、IME ONでの入力中にESCキーを押下するとコード実行が中断されます。

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
　　　　　　　　　　　　　　 ByVal Shift As Integer)
　　Application.EnableCancelKey = xlDisabled
End Sub

このようにイベントプロシージャの冒頭にCancel割り込みの制御をする[EnableCancelKey プロパティ]を入れても効果はありません。
IMEが関係しているのか理屈はよくわかりませんが、ComboBoxでも同様です。UserForm内では発生しないようなので、Sheet上のコントロールに限定したものなのでしょうけど。
#シート上にMSFormsコントロールを配置するのはあまり推奨されてませんし、私もあまり使わないので遭遇した事はなかったのでした。

とりあえず EnableCancelKey = xlDisabled を維持した状態で ChangeイベントやKeyDownイベントが起こるようにすれば回避できるようです。

'SheetModule
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private flg As Boolean
'-------------------------------------------------
Private Sub TextBox1_GotFocus()
　　Application.EnableCancelKey = xlDisabled
　　flg = True
　　Do
　　　　'CPU使用率の緩和
　　　　Sleep 1
　　　　DoEvents
　　Loop Until Not flg
End Sub
'-------------------------------------------------
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
　　　　　　　　　　　　　　 ByVal Shift As Integer)

End Sub
'-------------------------------------------------
Private Sub TextBox1_LostFocus()
　　'GotFocusイベントのLoop終了条件
　　flg = False
End Sub

GotFocusイベントが走りっ放し、つまりxlDisabled状態でKeyDownイベントが発生する事になります。

■Worksheet削除イベント？

シートが削除された時のイベントを捉えたいというニーズはあるのでしょうか。
最近のケーススタディで、ある特定のシート削除をトリガーに、というのがありました。
最初に考えたのは、

'ThisWorkbook Module
Option Explicit
Const chk = "監視シート" '監視対象シート名
'-------------------------------------------------
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
　　If Sh.Name = chk Then
　　　　Application.OnTime Now, Me.CodeName & ".shtchk"
　　End If
End Sub
'-------------------------------------------------
Private Sub shtchk()
　　Dim ws As Worksheet

　　On Error Resume Next
　　Set ws = Sheets(chk)
　　On Error GoTo 0
　　If ws Is Nothing Then
　　　　MsgBox "削除"
　　Else
　　　　Set ws = Nothing
　　End If
End Sub

こんな感じ...
OnTimeメソッドを使って監視対象シートのDeactivateイベントが終了した直後にシート名をキーに存在チェックをかけるというものです。
SheetDeactivateが発生しないVBAコードでの削除やシート名変更の場合などは考えない前提です。

でもよく考えてみると、Calculateイベントを使ったほうが簡単なようです。
ダミーシートを準備して、そのA1セルに
=監視シート!IV65536
など、監視したいシートを参照する数式を入れ、そのダミーシートのシートモジュールのCalculateイベントを使います。

'Sheet Module
Option Explicit

Private Sub Worksheet_Calculate()
　　Dim ws As Worksheet
　　Dim x　As String

　　With Range("A1")
　　　　If IsError(.Value) Then
　　　　　　x = .Formula
　　　　　　On Error Resume Next
　　　　　　Set ws = Sheets(Mid$(x, 2, InStr(x, "!") - 2))
　　　　　　On Error GoTo 0
　　　　　　If ws Is Nothing Then
　　　　　　　　.ClearContents
　　　　　　　　MsgBox "削除"
　　　　　　Else
　　　　　　　　Application.EnableEvents = False
　　　　　　　　.Formula = "=" & ws.Name & "!IV65536"
　　　　　　　　Application.EnableEvents = True
　　　　　　　　Set ws = Nothing
　　　　　　End If
　　　　End If
　　End With
End Sub

工夫すれば行列の挿入｜削除イベントにも使えそうです。

■Application.InputBoxで他Book参照

Type 8 のApplication.InputBoxメソッドを使ってセル参照を取得する時、InputBox表示中にBookを切り替えるにはメニューの[ウィンドウ]から切り替えます。
またはウィンドウの整列や、最大化解除で別Bookを表示させたりしても良いです。
[Ctrl]+[TAB]キーや[ウィンドウをタスクバーに表示]させて選択しても切り替わらないので、できないという思い込みが先行しがちな事があるかも？

Sub test()
　　Dim r As Range

　　On Error Resume Next
　　Set r = Application.InputBox("他Bookの選択は ワークシートメニューバーの" _
　　　　　　　　　　　　　　　 & "[ウィンドウ(W)]で できます。", Type:=8)
　　On Error GoTo 0
　　If Not r Is Nothing Then
　　　　MsgBox r.Address(external:=True)
　　　　Set r = Nothing
　　End If
End Sub