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

Question

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

Answer 1

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