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

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

"Need Help Fast!" wrote:

> 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

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

"CubsFan" wrote:

> 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
>
> "Need Help Fast!" wrote:
>
> > 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

On Mar 21, 10:16 am, Need Help Fast!
<NeedHelpF...@discussions.microsoft.com> wrote:
> 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

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

"matt" wrote:

> On Mar 21, 10:16 am, Need Help Fast!
> <NeedHelpF...@discussions.microsoft.com> wrote:
> > 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
>
>

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

Thanks everyone for your help.

"merjet" wrote:

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