Macro Error

[bigme]

good morning,
need your advise regarding to my macro problem.
i have a macro that doesn't work if i run from the VIEW Menu, it's always show "Subscript out of range" but in the other way, if i run from the VB editor it's work fine.
if there anybody have the same problem with me? maybe you can share your experience, thank you.

regards,
bigMe

 

[Seemanto Saha]

Hello bigme,
Thanks for sharing your problem with us. The "Subscript out of range" error can have a few different causes, and the behavior you described, where it runs from the Visual Basic Editor but not from Excel's View tab, may be related to the scope and visibility of objects or variables in your code.

Some common reasons why you may encounter the error are:

• If the macros are disabled or the workbook is unblocked due to Excel's security settings.
• If your code relies on a specific workbook or worksheet being open, and it's not open when you run the code from Excel's View tab, you may encounter a "Subscript out of range" error.
• Incorrect referencing in worksheets, ranges, or other objects.

Please, click the Debug button in the error message dialog box and check which line is highlighted. It is not possible to surely say what went wrong without seeing the line(s) where errors have occurred.



Regards,
Seemanto Saha
ExcelDemy

 

[bigme]

dear Seemanto,
thank you for the explanation, for your information the error always refer to Set sh1 = Worksheets ("DATABASE"), is there any miss type in my code?

regards,
bigMe

 

[Seemanto Saha]

dear Seemanto,
thank you for the explanation, for your information the error always refer to Set sh1 = Worksheets ("DATABASE"), is there any miss type in my code?

regards,
bigMe

Hello bigme,

Thanks for your feedback. From the screenshots you provided in the first post, I understand that you want to add new sheets and add data to specific sheets in a Master.xlsx file from a .xlsm file. You wrote the necessary code in a Private Subroutine and then called it within another Subroutine.​

I wrote a similar code in a .xlsm file.

Private Sub test1()
    Dim sheetname As String
    sheetname = InputBox("Enter sheet name")
    With Workbooks("Masterfile.xlsx")
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sheetname
        Set sh1 = Worksheets("Test 1")
        Set sh2 = Worksheets("New Code")
        sh1.Range("A1") = "Movie"
        sh2.Range("A1") = "Budget"
    End With
    sh1.Range("A2") = "Actor"
    sh2.Range("A2") = "Director"
End Sub

Sub newcode()
    Call test1
End Sub

After Running this code from Excel’s View tab, it showed the “Subscript out of range” error in the following line.​

Set sh1 = Worksheets("Test 1")

But when I Ran this code from the Visual Basic Editor window, sometimes it worked properly and sometimes showed an error in the same line. These are the same things you encountered. However, I noticed another thing. When I Ran this code from the .xlsx file’s View tab, it worked properly.

The reason behind encountering this error is related to the way Excel handles the scope and accessibility of worksheets between different workbooks when you're running macros from different workbooks.

When you Run a macro from the View tab in Excel, it considers the active workbook as the context for executing the code. That means the .xlsm file where you wrote the code, becomes the active workbook, and it doesn't have direct access to worksheets in another workbook like "Master.xlsx" without some specific qualifications. It tries to find the DATABASE sheet within the .xlsm file (whereas the code should search the sheet within the Master.xlsx file) and returns a Subscript out of range error as the DATABASE sheet is not available in the .xlsm file.

Note: if you had a sheet named DATABASE in your .xlsm file, then the code would show an error in the next line (Set sh2 = Worksheets(“Memo Gudang”)) as the Memo Gudang sheet is not available within the .xlsm file.​

But if I Run this code from the Master.xlsx file's View tab, it works properly as it is now the active workbook.

To fix this issue and ensure that your code can Run successfully, you can modify your code to explicitly reference the worksheets within "Master.xlsx" by using the Worksheets collection of the specific workbook.​

Here is the modified code for my previous code.

Private Sub test1()
    Dim sheetname As String
    sheetname = InputBox("Enter sheet name")
   
    Dim masterfile As Workbook
    Set masterfile = Workbooks("Masterfile.xlsx")
   
    With masterfile
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sheetname
        Set sh1 = .Worksheets("Test 1")
        Set sh2 = .Worksheets("New Code")
        sh1.Range("A1") = "Movie"
        sh2.Range("A1") = "Budget"
    End With
   
    sh1.Range("A2") = "Actor"
    sh2.Range("A2") = "Director"
End Sub

Sub newcode()
    Call test1
End Sub

In this modified code, I explicitly set a reference to the "Masterfile.xlsx" workbook as masterfile, and then we use this reference to access worksheets and ranges within the Masterfile.xlsx workbook. This way, the code will work regardless of which workbook is currently active.​

Similar to this, you should also set the reference of the Master.xlsx workbook explicitly.

Hopefully, I was able to provide you with a clear idea of why the error occurred and how you can fix it. Let us know your feedback.​

Regards,
Seemanto Saha
ExcelDemy