arraysexcelvbatype-mismatch

VBA. Passing A1 = Transpose(range(“a1-n1” to a function


So the first array works fine but when i pass the array to the procedure I am getting a type mismatch for the array. I obv dont understand.

Proc1() 
Dim Arr as Variant

Arr = Application.worksheetfunction.transpose(applicatiin.worksheetfunction.transpose(range(“a1-n1”)))

Dim lVC As Variant
For each lVC in Arr
  MsgBox lVC
Next lVC

Printarr(arr)

End Proc


Proc Printarr( ByRef l_arr() As Variant)

Dim VC As Variant
For each VC in l_arr
  MsgBox VC
Next VC

End Proc

I expect the procedure to loop through the array and show the value I have tried changing the array type. I even tried

Arr = thisworksheet.range(“a1-n1”).value

No go


Solution

  • In VBA the code should read:

    Sub Proc1()
        Dim Arr As Variant
    
        Arr = ActiveSheet.Range("a1:n1")
    
        Dim lVC As Variant
        For Each lVC In Arr
            MsgBox lVC
        Next lVC
    
        Printarr Arr
    
    End Sub
    
    Sub Printarr(ByRef l_arr As Variant)
        Dim VC As Variant
        For Each VC In l_arr
            MsgBox VC
        Next VC
    End Sub