Subscript out of range when declaring variable with filepath

Question

I am having issues when calling the name of the variable to save the new file as xlsb.

This is for a new master file I'm working for in Excel. I already tried with the real name of the file I'm using, but when I declare it as a variable, an error keeps popping up

Option Explicit
Dim roster As String


Sub DisplayRegion()

Dim SpecificArea As String

SpecificArea = ActiveCell.CurrentRegion.Select

With Selection
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With


End Sub

Sub roster_conversion(roster As String)
'
' roster_conversion Macro
'

'

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets("Sheet1").Select
    Range("A1").Select
    Workbooks.Open Filename:=roster
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

    DisplayRegion

    Workbooks(roster).SaveAs Filename:="Roster", FileFormat:=50
    Workbooks(roster).Close SaveChanges:=True

    Windows("Control.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Sub roster_calling()

roster = "C:\Users\ricardo.delgado\Downloads\Global L&D Roster Report.xlsx"

roster_conversion roster

End Sub

I expect the code to run smoothly as it was before. Now I am declaring the file inside a variable, and it keeps giving me the Subscript out of range error. Could you guys help me?

Your variable `Roster` appears to be locale. Either declare it global or pass it to `roster_conversion` function. — Guest, Jul 23 '19 at 16:47
Thanks for the help. I just updated the post with the full code to show the declaration of my variables, I keep on getting the same error though. Any thoughts? — sati_space, Jul 23 '19 at 17:16
`DisplayRegion`has a few errors, and it does not come clear what it is used for.. — Guest, Jul 23 '19 at 17:21
yes, I was missing some code but now it's all complete. DisplayRegion calls the method listed before to unwrap text from the file. It works fine though, the problem comes after this, when I want to save the file with a new name — sati_space, Jul 23 '19 at 17:33

Vityata · Answer 1 · 2019-07-23T18:18:55.310

2

The variable roster could be passed to roster_conversion as parameter. There it could be used:

Sub RosterCalling()

    Dim roster As String
    roster = "C:\Users\gropc\Desktop\1.xlsx"
    RosterConversion roster

End Sub

Sub RosterConversion(roster As String)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim wkb As Workbook
    Set wkb = Workbooks.Open(Filename:=roster)
    wkb.Worksheets(1).Rows("1:1").Delete Shift:=xlUp
    wkb.SaveAs Filename:="Roster", FileFormat:=50
    wkb.Close SaveChanges:=True

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Additionallly - How to avoid using Select in Excel VBA

edited Jul 23 '19 at 18:18

answered Jul 23 '19 at 16:46

Vityata

42,633
8
55
100

I did declared it at the top of the code as Option Explicit Dim roster As String as a global variable and didn't work. I have changed the code to what you sent me, but it doesn't seem to work. Keeps giving the same error... any thoughts on what might be wrong? – sati_space Jul 23 '19 at 17:07
just edited the post with the full code now. My bad, newbie mistake – sati_space Jul 23 '19 at 17:14
@sati_space - actually I did not look through the whole code, I only fixed the `RosterCalling()`. Now I have removed all the "noisy" `Select` and `Activate`, assiging the `roster` variable to `wkb`, thus it should work. – Vityata Jul 23 '19 at 18:20
1

Thank you for your help! Worked like a charm. – sati_space Jul 23 '19 at 18:51

Subscript out of range when declaring variable with filepath

1 Answers1