-
Feb 6th, 2013, 06:57 AM
#1
Thread Starter
Junior Member
[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
-
Feb 6th, 2013, 07:41 AM
#2
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:
evaluate to after that statement?
-
Feb 6th, 2013, 08:18 AM
#3
Thread Starter
Junior Member
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)
-
Feb 6th, 2013, 08:42 AM
#4
Re: Run time error - problems with my vba?
Originally Posted by InderpalHothi
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...
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|