Anyone know anything about arrays and vba? If so, please help

[Guest]

What I am trying to do, is call on array and go down it and use these values
for another part of my macro. Everything works fine but this. Here is the
code.

Option Explicit

Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet

Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2



Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="

Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"

Public Sub Run(dbPath As String)
Dim lDataRow As Long
Dim lData As Long
Dim GetAllData As Variant
Dim asData As String





ConnectToDatabase dbPath

Set GetAllData = asData()

'Stuff in Main that opens Excel



For lDataRow = 0 To UBound(asData(0))
Main asData(lDataRow, STATE_FIPS_COL), asData(lData, COMMODITY_COLUMN),
asData(lData, PRACTICE_COL)

'RunSolver
'Save as new workbook
Next lDataRow

End Function

It keeps giving me an error and saying "expected array". It says the array
isn't there, but it is. It's in the worksheet15. So if anyone can help it
would be greatly appreciated. Can someone tweak my code or something? Thanks

 

[Guest]

Try changing:

Dim asData As String

to:

Dim asData() As String

also, try placing it as a global above your sub 'Run'.

This might help, but I don't see the code that puts the recordset data into
the variable asData. If its a recordset that is filled in the
'ConnectToDatabase' routine, you might have to step through the set and get
the data out. Also, try just assigning the data instead of 'Set" --i.e.
GetAllData = asData

hope this helps
CF

 

[Guest]

Thanks for your help. I did what you said and now it stops on UBound and is
asking the same thing. Here is the code:



Option Explicit

Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet

Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2



Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="

Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"

Public Sub Run(dbPath As String)
Dim lDataRow As Long
Dim lData As Long
Dim GetAllData As Variant
Dim asData() As String





ConnectToDatabase dbPath

GetAllData = asData

'Stuff in Main that opens Excel



For lDataRow = 0 To UBound(asData(0))
Main asData(lDataRow, STATE_FIPS_COL), asData(lData, COMMODITY_COLUMN),
asData(lData, PRACTICE_COL)

'RunSolver
'Save as new workbook
Next lDataRow

End Function

 

[matt]

What I am trying to do, is call on array and go down it and use these values
for another part of my macro. Everything works fine but this. Here is the
code.

Option Explicit

Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet

Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2

Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="

Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"

Public Sub Run(dbPath As String)
Dim lDataRow As Long
Dim lData As Long
Dim GetAllData As Variant
Dim asData As String

ConnectToDatabase dbPath

Set GetAllData = asData()

'Stuff in Main that opens Excel

For lDataRow = 0 To UBound(asData(0))
Main asData(lDataRow, STATE_FIPS_COL), asData(lData, COMMODITY_COLUMN),
asData(lData, PRACTICE_COL)

'RunSolver
'Save as new workbook
Next lDataRow

End Function

It keeps giving me an error and saying "expected array". It says the array
isn't there, but it is. It's in the worksheet15. So if anyone can help it
would be greatly appreciated. Can someone tweak my code or something? Thanks

It's asking for an array because of the line that reads "Set
GetAllData = asData()" The left parenthesis "(" and the right
parenthesis ")" on the end of the "asData" variable signal to Excel
that you have an array. This is problematic for two reasons. (1)
When you declared your variables you declared "Dim asData As String"
and not "Dim asData() As String" and (2) if you do not define the size
of the array at declaration, you must use the "ReDim" statement to
define the array size.

Thus, if you define the array at declaration you can state "Dim
asData(10) As String" and the array will hold 11 items. It holds 11
because the default for the option base is 0. (You can change this to
be 1 by stating "Option Base 1" and then the array would hold 10
items). Or, if you don't define the array size at declaration (i.e.
"Dim asData() As String") then you can use the following line
somewhere inside the sub procedure: "ReDim asData(10)"

The array needs a size before you can start doing things with the
array.

Hopefully this makes sense and helps clear up some of your confusion.

Matt

 

[Guest]

Thanks for the reply. I've done everything you and Cubs fan told me to do. At
the UBound statement it is asking for the same thing. Is there something I
need to do with it? Thanks

 

[merjet]

UBound(asData(0)) is improper. If asData is 1-dimensional, it should
be UBound(asData). If asData is 2-dimensional, as it appears, it
should be UBound(asData, _ ). Fill in an appropriate number, probably
0 or 1, depending on how you ReDim'd asData.

Also, you can't start with Sub and use End Function.

Hth,
Merjet

 

[Guest]

Thanks everyone for your help.