Array declaration question - "myArray()" versus "myArray"

[CubsFan]

I am trying to assign a dynamic range of cell values to an array. In that effort, I discovered one of my errors was how I dim the array, but I do not understand the reason why my original method does not work.

The pertinent portion of my original code was as below but I received a type mismatch.

Sub DataConversion()

Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()

    With Sheets("Copy of Source Data")
    
        LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...        
        Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...              
        arrayInput = rngInput
...
    End With
...
end sub

However, both of the following work:

Sub DataConversion()

Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()

    With Sheets("Copy of Source Data")
    
        LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...        
        Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...           
        arrayInput = Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
    End With
...
end sub

AND

Sub DataConversion()

Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput

    With Sheets("Copy of Source Data")
    
        LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...        
        Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...               
        arrayInput = rngInput
...
    End With
...
end sub

So, what gives? If I dim arrayInput with "()" AND I set the array using the .Range method, it works but by storing the range to a variable first does not? But, if I dim without "()" it works? I obviously am not understanding the declaration of the array fully.

Can someone help me understand why dim arrayName()... arrayName = Range(x:y) works but arrayName = rngVariable does not? Thank you!

CubsFan

[Paul_Hossler]

Assuming that the last 'As ...' applies to all the variables on the line is a carry over from other languages

Each Dim variable needs a 'As <something>' otherwise it's Variant

inputArray is a Variant containing an array of Variants

rngInput is a Variant containing an Object of type Range


Capture.JPG



Look this over

Option Explicit

Sub DataConversion_1()
     
    Dim rngInput As Range, rngOutput As Range   '<<<<<<<<<<<<<<<<<<<<<<<
    Dim LastInputRow As Long, LastInputColumn As Long, i As Long, j As Long, counter As Long
    Dim arrayInput As Variant    '<<<<<<<<<<<<<<<<<<<<<<<<<
     
    MsgBox VarType(rngInput)
     
    With Sheets("Copy of Source Data")
         
        LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
        arrayInput = rngInput
    
        MsgBox IsObject(rngInput)
        MsgBox IsObject(arrayInput)
        MsgBox IsArray(arrayInput)
    
        For i = LBound(arrayInput, 1) To UBound(arrayInput, 1)
            For j = LBound(arrayInput, 2) To UBound(arrayInput, 2)
                MsgBox i & " --- " & j
            Next j
        Next I
    
    
    End With
End Sub

[snb]

Or

Sub M_snb() 
  sn=Sheets("Copy of Source Data").cells(1).currentregion

  msgbox typename(sn)
End Sub

[CubsFan]

I thank you both for the education! :-) Now I can go about my business again without the two-year-old in my brain saying "but why?"