Results 1 to 4 of 4

Thread: [RESOLVED] Run time error - problems with my vba?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Resolved [RESOLVED] Run time error - problems with my vba?

    Guys,

    Don't know what is going on with this code - is there a problem with my vba?


    Public Sub Declarations()

    Dim wb As Workbook
    Dim wbS As Workbook

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet

    Dim wsS As Worksheet

    Set wb = ThisWorkbook

    Set ws1 = wb.Worksheets("Main")

    Dim FileLoc As Range

    Set FileLoc = ws1.Range("FileLoc")

    End Sub


    Sub Gather_Data()

    Call Declarations

    Set wbSource = Workbooks.Open(FileLoc)




    When I run Gather_Date, i keep on getting a run time error, it does not know what fileloc is.

    It is not remebering the values assigned to variables when i call a function from another? Cant figure this out -never had this issue before.

    Thanks for your help






    End Sub

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run time error - problems with my vba?

    Code:
    Set FileLoc = ws1.Range("FileLoc")
    To what does the "FileLoc" on the right side of the statement refer? Is it a named range?

    If you step through the code, what would this:

    Code:
    FileLoc.Address
    evaluate to after that statement?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Run time error - problems with my vba?

    Object required error.

    FileLoc range has been set to the exact file path.

    If I combine the whole thing in one subroutine as such:

    Public Sub Declarations()

    Dim wb As Workbook
    Dim wbS As Workbook

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet

    Dim wsS As Worksheet

    Set wb = ThisWorkbook

    Set ws1 = wb.Worksheets("Main")
    Set ws2 = wb.Worksheets("Inventory")
    Set ws3 = wb.Worksheets("Stock Borrows")


    Dim FileLoc As Range

    Set FileLoc = ws1.Range("FileLoc")

    I get no errors and it works fine. Its just when i try to do it seperately as per above, i.e. set variables in one subroutine that i can call to use in other macros..


    Set wbSource = Workbooks.Open(FileLoc)

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Run time error - problems with my vba?

    Quote Originally Posted by InderpalHothi View Post
    Guys,

    Don't know what is going on with this code - is there a problem with my vba?

    Code:
    Public Sub Declarations()
    
    Dim wb As Workbook
    Dim wbS As Workbook
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    
    Dim wsS As Worksheet
    
    Set wb = ThisWorkbook
    
    Set ws1 = wb.Worksheets("Main")
    
    Dim FileLoc As Range
    
    Set FileLoc = ws1.Range("FileLoc")
    
    End Sub
    
    
    Sub Gather_Data()
    
    Call Declarations
    
    Set wbSource = Workbooks.Open(FileLoc)
    
    End sub



    When I run Gather_Date, i keep on getting a run time error, it does not know what fileloc is.

    It is not remembering the values assigned to variables when i call a function from another? Cant figure this out -never had this issue before.

    Thanks for your help
    The reason is that the variable have been declared in the first sub. The second sub cannot see them. You need to read up on Scope of variable and declare them outside the subs as private or public variables.

    A second problem might be that fileloc being a range may not have a valid string for opening the workbook, but I guess you will get to that if it is a problem...

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width