Dynamic DIM names - is it possible?

[Rocky0201]

I have approximately 200 variables that I need to populate with values from one of my sheets at initialization. Currently, I have approximately 200 "if" statements to perform this task. example:

In Module 1 (only has my Public definitions):

Public CRptNameC as Integer
Public CRptNameR as Integer
.
.
etc

In Sheet 1 (my vb code):

CRptNameC = Sheets("ReportParms").Cells(1, 2)
CRptNameR = Sheets("ReportParms").Cells(1, 3)
.
.
etc

Is there a more efficient means to load my Public definitions without having to explicitly define each named integer or string?

Thanks...

 

[NateO]

Hello,

You can assign a Range to an Array, but it has to be a Variant. E.g.,

Sub foo()
Dim varArr() As Variant, i As Long
Let varArr = Range("A1:E1").Value
For i = LBound(varArr, 2) To UBound(varArr, 2)
    Debug.Print varArr(1, i)
Next
End Sub

 

[Rocky0201]

Thanks Nate.... Building the array works great!!

Question now is... how best to access the array?

I would like to keep the names that I currently use i.e.;

Cells(TBlankR, TBlankC) = StatusBlankL

Where TBlankR and TBlankC were defined as:

Public TBlankR As Integer, TBlankC As Integer

Now that I have an array, how do I have TBlankR and TBlankC point to a section of the array?

In the module where my array is defined; ParmArray() as Integer do I also place;

TBlankR = ParmArray + x

Where x is the offest?

Thanks again...

 

[NateO]

You're welcome. You'd have to populate those two Variables with numeric values.

Keep in mind in my example, and in general, this Variant Array mimics the Cells Property. So,

Cells(1,2)

Populates to:

varArr(1,2)

Make sense?

 

[Rocky0201]

well... sorry Nate.. I'm a bit confused... simple minds, you know...

Here's what I have:

Sub GetReportParms()
Dim LR As Long
LR = Sheets("ReportParms").Cells(Rows.Count, 1).End(xlUp).Row
ii = 1
For i = 2 To LR
If Cells(i, 5) <> "" Then
ReDim Preserve ParmArray(1 To ii)
ParmArray(ii) = Cells(i, 5)
ii = ii + 1
End If
Next i
End Sub

This builds my ParmArray beautifully...

What I'm trying to do is index into the array using something like a label...

I have in my Public definition Module;

Public ParmArray() As Integer

In the same Module, I am trying to point a previously defined public integer to a section in the array. if i didn't get compile errors, it would look something like;

Public RRptR(ParmArray(x))
x = offset into the array

I know this is not a valid statement but my goal is to use all of the previously defined names (RRptR, etc) that is throughout my vb code.

Any suggestions?

Thanks again...

 

[NateO]

I'm quite following what you're trying to declare. Wouldn't you make the numerical offset a constant (or variable) and read from the Array at run-time?

It looks like you're trying make a dynamic Array element a static Variable?

 

[Rocky0201]

you're correct Nate... I am trying to read from the array at run time. I was looking for "the easy way out" and point to an offset in the array that, at run time, would extract the value at the offset.

Instead of trying to point to an offset i.e:

Cells(TBlankR, TBlankC) = StatusBlankL
where TBlankR and TBlankC would point to an offset in the array... would I code it like:

Cells((ParmArray,1),(ParmArray,2)) = StatusBlankL

Thanks....

 

[Rocky0201]

Wait... i think i understand what you're asking...

The position in the array is constant for.. say TBlankR or TBlankC. the value at those locations in the array would change....

 

[NateO]

So, as an example, something like this?

Public Const TBlankR As Long = 2
Public Const TBlankC As Long = 4

Sub foo()
Dim varArr() As Variant
Let varArr = Array(1, 2, 3, 4, 5)
MsgBox varArr(TBlankR)
MsgBox varArr(TBlankC)
End Sub

You want to be careful, because I created a 2d Array in my first example, and you're creating a 1D Array.

 

[Rocky0201]

Thanks Nate...

I'll give this a try and let yo uknow how it works out...

Many thanks for helping me Nate...