Dynamically declare and populate variables

[Gary@Dwight]

I have a worksheet that lists database fields, corresponding excel variables
and the data type for each. I'd like to run through the list of variables
and declare each one with the appropriate data type. I've pasted the code
snippet below:

'Load the Variables into an array
ReDim DataVars(HowManyCols) As String
ReDim DataOffset(HowManyCols) As String

Dim Counter As Integer
Counter = 1

'Dimension data and column offset variables
For Each V In VarRange
DataVars(Counter) = V.Value
Select Case V.Offset(ColumnOffset:=1)
Case Is = "Long"
Dim DataVars(Counter) As Long
Case Is = "String"
Dim DataVars(Counter) As String
Case Is = "Date"
Dim DataVars(Counter) As Date
Else
Dim DataVars(Counter) As Variant
End Select

DataOffset(Counter) = V.Value & "_OFF"
Dim DataOffset(Counter) As Integer
Counter = Counter + 1
Next V

When it hits the Dim statement, the Complie error is: Constant expression
required.

Any help you can offer would be greatly appreciated.

 

[Jim Thomlinson]

I think you have missed the point of arrays. Arrays are a collection of like
things. In this case the best you could do would be an array of variants. You
need to declare the array up front and then you can redim it as you need to
resize it.

 

[Gary@Dwight]

Thanks Jim:

All of the elements in the array are the same type: strings. What I'm
trying to accomplish is to run through a list of variable names (call that
Column A), and declare each variable according to the type in Column B.

Can you think of another way to accomplish this?

Regards,
Gary@Dwight

 

[Jim Thomlinson]

Dim DataVars() as String
ReDim DataVars(HowManyCols)
Declares an array of strings.

Then later on you try to re-declare using
Dim DataVars(Counter) As Long
You are trying to redeclare elements of the array with different data types
and that is not allowed. Your array DataVars can only be 1 data type.

Why not delcare it as an array of Variants and then you can put whatever
data you want into each of the elements. While geneally it is true that you
want to avoid using varaints as they have more overhead there are times when
variants are the best option.

Finally if we had some idea what you were trying to accomplish (big picture)
then we could possibly give you better suggestions.

 

[Gary@Dwight]

The big picture is:
Users will import data from a CSV file with column headings into a page of
the spreadsheet. I don't necessarily know which fields they will import each
time, but I do have a finite list of fields. This data will be reviewed and
uploaded to an Oracle database.

I'd like to compare the column heading of the CSV file to the available
columns on my "Data Map" sheet to determine where it will be stored in Oracle
and also to do some validation of the data.

I wanted to avoid a hard coding as much as possible as I intend to use this
same logic in many other sheets. The first problem I encountered was simply
being able to Dim a variable and provide a name:

Dim {the variable name in C15} as {data type in C16}

Thanks for looking at this. Any direction would be appreciated...

 

[Jim Thomlinson]

What do you intend to do with these variables once they are declared? The
reason that I ask is that you can't declare variables as you describe.
Declaring variables is an exercise in hard coding. You can not define your
variable names or data types at run time, only at design time.

I have done similar things to what you ask but I have never needed to
declare variables for each column of data. I traverse the cells in the column
using range objects, allowing me to validate the data. I write back the
records to the database using ODBC connections. The only issue there is that
your SQL statement must be able to handle variables to define the field
names. Finally when writing data you may have to do some coercion to get the
XL data into a format that Oracle will like.

 

[Gary@Dwight]

Thanks Jim...

The connection and upload process is not an issue. I've done that plenty of
times.
Your comments made me think of another solution to this, thanks for your
help...