Subscript out of range 
Author Message
 Subscript out of range

I have an Excel user form with a couple of controls (combo box, calendar
control) that are designed to pass selected variables as parameters to an
Access query using DAO. It works great with the parameters hard-coded, but
I'm struggling with trying to pass the values in as variables. The last line
of the following code generates a "Subscript out of range" error and the
Help message indicates a couple of possibilities. My guess is that the code
is "referencing a non-existent collection member", which I interpret to mean
the following public variables are not part of the collection.

If that's the case, how do I add the variables to the collection? Or do I
have something else going on, i.e. the Select Case is not the right tool?

Thanks - Any help is appreciated - RobertC

Public strQuery As String
Public strQueryName As String
Public strQueryParameters As String
Public strWorksheet As String

'// Establish variables

Private Sub cboSelectQuery_Change()

    strQuery = cboSelectQuery.Text
    Select Case strQuery
        Case Is = "JC Budgets to Date"
            strQueryName = "JC Budgets to Date"
            strQueryParameters = "Enter Transaction Date: mm/dd/yy"
            strWorksheet = "JC Budgets"
        Case Is = "JC Costs to Date"
            strQueryName = "JC Costs to Date"
            strQueryParameters = "Enter Accounting Date: mm/dd/yy"
            strWorksheet = "JC Costs"
        Case Is = "JC Detail to Date"
            strQueryName = "JC Detail to Date"
            strQueryParameters = "Enter Accounting Date: mm/dd/yy"
            strWorksheet = "JC Detail"
    End Select

Private Sub cmdLoadDate_Click()
    Dim Db As Database
    Dim Qd As QueryDef
    Dim Rs As Recordset
    Dim Ws As Object
    Dim i As Integer
    Dim Path As String
    Dim sTransDate As String
    Dim nRet As Long

    '// evalInput will return success or failure, and if successful,
    '// set passed string to the user-selected date and pass-in date
variable.

    nRet = EvaluateInput(sTransDate)

    '// Unload form and exit if input not valid

    If nRet <> 0 Then

    '// Err msg displayed in EvaluateInput()
    '// Unload the form and exit
        Exit Sub
    Else
        Unload frmSelectVariables
    End If

    '// Set the Path to the database.
    Path = "k:\database\IPP.mdb"

    '// Set the Worksheet

    Set Ws = Sheets(strWorksheet) = strWorksheet

    '// More code here



Wed, 04 Aug 2004 07:47:27 GMT  
 Subscript out of range
Your line:

    Set Ws = Sheets(strWorksheet) = strWorksheet

does not look right. What was it intended to do? It actually does a logical
test, which is nonsense, and tries to assign a True or False value to Ws.
Perhaps you meant:

    Set Ws = Sheets(strWorksheet)

However, the subscript out of range error indicates that the sheet specified
in strWorksheet does not exist in the active workbook. Perhaps you need to
qualify the reference with a workbook reference:

    Set Ws = Workbooks(strWorkbook).Sheets(strWorksheet)

--

John Green
Sydney
Australia


Quote:
> I have an Excel user form with a couple of controls (combo box, calendar
> control) that are designed to pass selected variables as parameters to an
> Access query using DAO. It works great with the parameters hard-coded, but
> I'm struggling with trying to pass the values in as variables. The last
line
> of the following code generates a "Subscript out of range" error and the
> Help message indicates a couple of possibilities. My guess is that the
code
> is "referencing a non-existent collection member", which I interpret to
mean
> the following public variables are not part of the collection.

> If that's the case, how do I add the variables to the collection? Or do I
> have something else going on, i.e. the Select Case is not the right tool?

> Thanks - Any help is appreciated - RobertC

> Public strQuery As String
> Public strQueryName As String
> Public strQueryParameters As String
> Public strWorksheet As String

> '// Establish variables

> Private Sub cboSelectQuery_Change()

>     strQuery = cboSelectQuery.Text
>     Select Case strQuery
>         Case Is = "JC Budgets to Date"
>             strQueryName = "JC Budgets to Date"
>             strQueryParameters = "Enter Transaction Date: mm/dd/yy"
>             strWorksheet = "JC Budgets"
>         Case Is = "JC Costs to Date"
>             strQueryName = "JC Costs to Date"
>             strQueryParameters = "Enter Accounting Date: mm/dd/yy"
>             strWorksheet = "JC Costs"
>         Case Is = "JC Detail to Date"
>             strQueryName = "JC Detail to Date"
>             strQueryParameters = "Enter Accounting Date: mm/dd/yy"
>             strWorksheet = "JC Detail"
>     End Select

> Private Sub cmdLoadDate_Click()
>     Dim Db As Database
>     Dim Qd As QueryDef
>     Dim Rs As Recordset
>     Dim Ws As Object
>     Dim i As Integer
>     Dim Path As String
>     Dim sTransDate As String
>     Dim nRet As Long

>     '// evalInput will return success or failure, and if successful,
>     '// set passed string to the user-selected date and pass-in date
> variable.

>     nRet = EvaluateInput(sTransDate)

>     '// Unload form and exit if input not valid

>     If nRet <> 0 Then

>     '// Err msg displayed in EvaluateInput()
>     '// Unload the form and exit
>         Exit Sub
>     Else
>         Unload frmSelectVariables
>     End If

>     '// Set the Path to the database.
>     Path = "k:\database\IPP.mdb"

>     '// Set the Worksheet

>     Set Ws = Sheets(strWorksheet) = strWorksheet

>     '// More code here



Wed, 04 Aug 2004 08:47:07 GMT  
 Subscript out of range
One of the best methods I've found of not encountering subscript out
of range errors is to use object variables.  If you haven't used
object variables before, I would recommend learning about them because
they are extremely useful.  From memory, Chip Pearsons page and j-walk
have good background on this stuff.

One of the most common errors to occur is when a user changes a
worksheet name.  If the sheet is originally called "Sheet1", ad you
have code that says:

Sheets("Sheet1").Select

Everything will work fine.  But if a user changes the name to
"Revenue" (or whatever), you'll get a subscript error.

If you look in the Project Explorer in the VBA Editor in a blank
workbook, you should see that the first worksheet object is described
as Sheet1(Sheet1).  If, in Excel, you change the name of the worksheet
to "Revenue" and then go back to the VBA Editor, you'll see that this
has changed to Sheet1(Revenue).  Revenue is the name that Excel refers
to the worksheet by, while Sheet1 is the actual worksheet object.

To avoid subscript errors then, code like this can be used:

    Dim TheSheet As Worksheet
    Set TheSheet=Sheet1
    Sheets(Sheet1.Name).Select

Regardless of the changes made to the worksheet name, you won't get a
subscript error, as it selects based on the value of the Name
property, rather than trying to find the specific string "Sheet1".  In
a blank workbook, Sheet1.Name will be "Sheet1".  When you change the
worksheet name to "Revenue", Sheet1.Name will equal "Revenue".

Hope this helps...

Clayton.



Wed, 04 Aug 2004 12:25:35 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Subscript out of range error

2. Subscript Out Of Range

3. Subscript out of range error

4. Subscript out of range error

5. AddShapeRecordDetected Error: Subscript out of range

6. Defining an array and getting Subscript Out of Range Error

7. array subscript out of range

8. help with subscript out of range error

9. subscript out of range? (floating array problem)

10. Xarray ended up with Subscript out of range in Asian locale

11. Visual Basic - Subscript Out Of Range

12. Problems With FlexGrid And Subscript Out Of Range Error

 

 
Powered by phpBB® Forum Software