PDA

View Full Version : [SOLVED] Array declaration question - "myArray()" versus "myArray"



CubsFan
01-22-2016, 01:05 PM
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
01-22-2016, 01:44 PM
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


15245



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
01-22-2016, 02:44 PM
Or


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

msgbox typename(sn)
End Sub

CubsFan
01-23-2016, 06:43 AM
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?"