iT邦幫忙

0

excel vba 資料型態不符合問題

請問各位大大,我在run一個VBA SUB在.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)偵錯有資料型態不符合,如下圖所示,請協助更正,謝謝!
https://ithelp.ithome.com.tw/upload/images/20191211/20123369mcFQ0eEIrU.png

Sub wsj()
Application.EnableEvents = False
Application.Interactive = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Dim i As Long, j As Long, time0#
time0 = Timer
Dim ary, x, y As Variant
With Worksheets("工作表1")
For i = 1 To 50
    .Range("h2") = i
    For j = 40 To 100 - i
        .Range("l2") = j
        x = .Range("m14")
        If x < 61 Then
           y = .Range("ba14")
           If y > 19 Then
              If IsArray(ary) Then
                 ReDim Preserve ary(2, UBound(ary, 2) + 1) As Variant
              Else
                 ReDim ary(2, 0) As Variant
              End If
                  ary(0, UBound(ary, 2)) = i
                  ary(1, UBound(ary, 2)) = j
                  ary(2, UBound(ary, 2)) = x
           End If
        End If
    Next j
    If (i Mod 10) = 0 Then
       Debug.Print ("期數 i = " & i)
    End If
Next i
.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End With
Application.EnableEvents = True
Application.Interactive = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
MsgBox "執行時間" & Application.Round((Timer - time0) / 3600, 0) & "分"
End Sub
字太小。
請問錯誤訊息為何?

1 個回答

0
marlin12
iT邦新手 1 級 ‧ 2019-12-11 18:49:01
最佳解答

原因是ary還未被定義為"排列"(即是說未有執行過ReDim這兩行程式),到後面執行UBound(ary, 2)時,便會產生"資料型態不符合"問題。

要避開這個問題,可以加入以下修改:

If VarType(ary) = (vbVariant Or vbArray) Then  '確定ary是Variant裏的排列
    .Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End If

原來還有VarType()函數,我自己是都用IsArray()函數來判斷。

If IsArray(ary) Then
    .Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End If

我要發表回答

立即登入回答