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

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.

Code:

---

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:

Code:

---

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

Code:

---

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

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


Attachment 15245



Look this over

Code:

---

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

---

Or

Code:

---

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

  msgbox typename(sn)
End Sub

---

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?"