We help IT Professionals succeed at work.

Subscript Out of Range error racking my brain!

Hi. I know that in VBA, a subscript out of range error when trying to reference an object means it cannot find it.  Mine exists.  Please help because I keep getting the error with my variable call which works in other parts of the code.

Here is the code:

Public Sub Pop_trans_amount(TEMPLATE_NAME As String, TempPath As String)
   
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    While (True)
        totalpath = fso.BuildPath(TempPath & "\", "trans_amount.xls")
        If fso.FileExists(totalpath) Then GoTo Populate
    Wend

Populate:
    NewFile = TempPath & "\trans_amount.xls"
    Application.Workbooks.Open (NewFile)
    ActiveSheet.Select

    Sh_name = ActiveSheet.Name
    Cnt = ActiveSheet.UsedRange.Rows.Count - 1
    FullRange = ActiveSheet.Range(Range("A2:F2"), Range("A2:F2").End(xlDown)).Select
    If Cnt > 3 Then
        Workbooks(TEMPLATE_NAME).Activate <************THIS IS WHERE IT ERRORS
        Sheets("3. Other Fees").Activate
        Call Ins_Row("FullRange", Cnt - 4, "3. Other Fees")
        Application.CutCopyMode = False
    End If
   

Thanks for the help!

wander
Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
What line generates the error?

~bp

Author

Commented:
Workbooks.(TEMPLATE_NAME).Activate

I labeled it with asterisks to the right of it in the code above.

Regards
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, if the error is here:
Workbooks(TEMPLATE_NAME).Activate

This means that a workbook with the name that is passed to TEMPLATE_NAME must exist.

To verify that, with the requires workbooks open, run this code:
Sub ShowWBs
For intWB = 1 To Workbooks.Count
    MsgBox Workbooks(intWB).Name
Next
End Sub

Open in new window


and it will show the name of the workbook, and you must match that exactly when you pass it to your procedure.

Regards,

Rob .
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Sounds like the workbook that you are referencing by the passed parm TEMPLATE_NAME isn't available, are you sure you have the value correct?

~bp

Author

Commented:
Yes i have the workbook open. If i substitute the variable for just the file name, i get an object required error. When i put the complete path, equivalent to the variable, i get the subscript error...

Commented:
if the workbook hasn't been saved yet then the name is i.e. "theworkbook" otherwise it is "theworkbook.xls"
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Did what Rob's test displayed match the value being passed?

~bp

Author

Commented:
Robs test returned the workbook name.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Do you have any special characters in it?  Are you passing it as a string?

If you run this, passing the same workbook name, does it match or not?

Regards,

Rob.
Sub ShowWBs(TEMPLATE_NAME)
blnFound = False
For intWB = 1 To Workbooks.Count
    If LCase(TEMPLATE_NAME) = LCase(Workbooks(intWB).Name) Then blnFound = True
    strWBs = strWBs & vbCrLf & LCase(Workbooks(intWB).Name)
Next
If blnFound = True Then
	MsgBox TEMPLATE_NAME & " was found." & vbCrLf & "Workbooks open: " & strWBs
Else
	MsgBox TEMPLATE_NAME & " was not found." & vbCrLf & "Workbooks open: " & strWBs
End If
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Sorry, change
Sub ShowWBs(TEMPLATE_NAME)

to
Sub ShowWBs(TEMPLATE_NAME As String)

and run it using
ShowWBs "YourWorkbookName"

Rob.

Author

Commented:
If i run it as the name, i get the same error. Strange! When i am debugging, it will highlight that line and then upon pressing f8, that is when i error. That refers to the highlighted line and not the line after right? Just making sure it is the workbook activation and not the sheet reference on the next line...
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hard to say what you're looking at, but yes, I beleive the current line is highlighted in yellow while you're debugging.

You could change this:

        Workbooks(TEMPLATE_NAME).Activate <************THIS IS WHERE IT ERRORS
        Sheets("3. Other Fees").Activate
        Call Ins_Row("FullRange", Cnt - 4, "3. Other Fees")


to this

        MsgBox "Activating workbook " & TEMPLATE_NAME
        Workbooks(TEMPLATE_NAME).Activate
        MsgBox "Activatint sheet 3. Other Fees"
        Sheets("3. Other Fees").Activate
        MsgBox "Calling Ins_Row procedure"
        Call Ins_Row("FullRange", Cnt - 4, "3. Other Fees")

that way, you will know by the prompts which line has the error.

Rob.

Author

Commented:
Thanks for the tip. Yes it is on the line i thought all along.
Most Valuable Expert 2012
Top Expert 2014

Commented:
So I'm confused then....when you run the code I posted in comment ID 37235573, you should see a msgbox with something like:

<yourtemplate> was found.
Workbooks open:
<workbook1>
<workbook2>

does the template name and one of the workbook names match exactly?

Rob.

Author

Commented:
Yes it names the template filename. I am debugging and all the variables are being filled correctly but it just errors on that line. Dont know if it goes out of scope because it is in the 'GoTo'
Most Valuable Expert 2012
Top Expert 2014

Commented:
It shouldn't.  The Workbooks object is global.

I can't think of any other reason why that wouldn't find the appropriate workbook.

In my code, if you change
      MsgBox TEMPLATE_NAME & " was found." & vbCrLf & "Workbooks open: " & strWBs

to this
      MsgBox TEMPLATE_NAME & " was found." & vbCrLf & "Workbooks open: " & strWBs
      Workbooks(TEMPLATE_NAME).Activate

does it activate the sheet and not cause any error?

Rob.

Author

Commented:
Should i test that code in my sheet?
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yeah sure.  You may as well test it in the same conditions.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Have you included the file extension (e.g. xls or xlt or xlsx) in the TEMPLATE_NAME variable? If not, try adding it.