current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am getting an error when trying to execute the following subroutine

"Runtime Error: 9, Subscript out of range"

its highlighting the first variable declaration. At first I thought it was due to the wrong datatype but changing and playing around with that had no luck.

I also tried both Cells & Range

Public vFolderPath As String
Public vCMFNewPath As String
Public vKBNewPath As String
Public vDPI As Integer

Private Sub SetGlobal()

Dim vGo As String
Dim vTemplateLocation As String
Dim vCMFFilename As String
Dim vKBFilename As String
Dim vDriver As String
Dim vPKG As String

    vDPI = Workbooks("tools.xlsm").Sheets("SETTINGS").Range("B2").Value

    vFolderPath = Workbooks("tools.xlsm").Sheets("SETTINGS").Range("B3").Value & "\"

Any ideas?

share|improve this question
    
Where are you running the sub from (within a worksheet or a module)? That can affect variable visibility. Try testing each variable individually to make sure you have spelled them correctly e.g. MsgBox Workbooks("tools.xlsm").Name, MsgBox Workbooks("tools.xlsm").Sheets("SETTINGS").Name - hopefully that will identify where the problem is. –  MattCrum Mar 22 '13 at 12:05
    
Is that the full code? Where's your End Sub? For the sake of removing the obvious: you DO have a workbook called tools.xlsm open, and that workboko does have a sheet named SETTINGS, right? –  Sam Mar 22 '13 at 12:58
    
The code is just the first lines as OP said that error occurs on first line. I am inclined to agree that the problem lies in the fact that the file and or tab are incorrectly named or do not exist. –  Skip Intro Mar 22 '13 at 13:44
    
Add these two lines before you set the vDPI variable value and let me know if either of them cause the same error. Set wb = Workbooks("tools.xlsm") and then Set ws = Workbooks("tools.xlsm").Sheets("Settings"). –  David Zemens Mar 22 '13 at 14:14

2 Answers 2

up vote 0 down vote

Try below code :

Public vFolderPath As String
Public vCMFNewPath As String
Public vKBNewPath As String
Public vDPI As Integer

Private Sub SetGlobal()


    Dim vGo As String
    Dim vTemplateLocation As String
    Dim vCMFFilename As String
    Dim vKBFilename As String
    Dim vDriver As String
    Dim vPKG As String

    Dim wkbSetting As Workbook, shtSetting As Worksheet

    On Error Resume Next
    Set wkbSetting = Workbooks("tools.xlsm")
    On Error GoTo 0

    On Error GoTo err_rout
    If Not wkbSetting Is Nothing Then

        On Error Resume Next
        Set shtSetting = wkbSetting.Sheets("SETTINGS")
        On Error GoTo 0

        On Error GoTo err_rout
        If shtSetting Is Nothing Then
            Err.Raise Number:=32, Description:="Sheets Settings not found"
        End If


        vDPI = CInt(shtSetting.Range("B2").Value)
        vFolderPath = shtSetting.Range("B3").Value & "\"

    Else
        Err.Raise Number:=31, Description:="Workbook - tools.xlsm not found"
    End If


Exit Sub
err_rout:
    MsgBox Err.Description, vbInformation

End Sub
share|improve this answer
up vote 2 down vote

Code works fine, running from a file called Tools.xslm. with a tab called Settings, an integer in cell B2 and a string value in cell B3.

This works when running from a module in Personal xlsb or from within Tools.xlsm. It works even if you do not declare any of the variables.

share|improve this answer
    
thks figured it out im working with versions of the file and somewhere it got renamed to tool.xlsm thks for all the help –  midaym Mar 22 '13 at 15:36
    
Glad you figured it out, nothing more frustrating than trying to fix things on multiple versions of files! –  Skip Intro Mar 22 '13 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.