Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
I'm still learning about scope and visibility of objects, but I thought I had
a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
I think I'd change
Function RangeValues(SheetName, RowA, ColZ) To Function RangeValues(SheetName as string, RowA as long, ColZ as long) "Bob Bridges" wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
I'd also add
Dim WSO as excel.worksheet 'For early binding Dim WSO as object 'For late binding "Bob Bridges" wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
Nah, because once I have the object error figured out (I don't suppose you
have any ideas about that?) I intend to substitute this: If TypeName(Sheet) = "Worksheet" Then Set wso = Sheet _ Else Set wso = wbo.Worksheets(SheetName) --- "Barb Reinhardt" wrote: I think I'd change Function RangeValues(SheetName, RowA, ColZ) to Function RangeValues(SheetName as string, RowA as long, ColZ as long) --- "Bob Bridges" wrote: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
Ok, two questions. No, maybe three:
1) Why? What benefit do I get from this that I don't have already? 2) How can I declare something twice as two different types? 3) Does this have anything to do with my problem? Because if you're solving my problem I want to know how, and if you're just sidetracking on good programming practices then my first question stands but I'll keep looking for the solution to my problem. --- "Barb Reinhardt" wrote: I'd add Dim WSO as excel.worksheet 'For early binding Dim WSO as object 'For late binding |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
The function in the general module doesn't know about wbo.
You could use: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub Then in the general module's function, make sure you refer to the correct variable: Option Explicit Function RangeValues(SheetName As String, RowA As Long, ColZ As Variant) _ As Variant Dim wso As Worksheet Set wso = Sheet1.wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function A couple of alternatives... #1. Move your public variables into a General module. Then they'll be able to be seen by any procedure in any module. #2. Pass the workbook as part of the function call. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wbo As Workbook Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues(wbo, "Map", 2, 4) End Sub and Option Explicit Function RangeValues(wbo As Workbook, SheetName As String, _ RowA As Long, ColZ As Variant) As Variant Dim wso As Worksheet Set wso = wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function (You could pass the workbook's name as a string if you wanted, too.) #3. Instead of passing strings to the function, pass the worksheet you want to use. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wks As Worksheet Set wks = Workbooks("MyWorkbook.xls").Worksheets("Map") Stns = RangeValues(wks, 2, 4) End Sub Option Explicit Function RangeValues(myWks As Worksheet, RowA As Long, ColZ As Variant) _ As Variant RangeValues = myWks.Cells(RowA, ColZ).Value End Function Bob Bridges wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
Seems to me you're telling me to change the progam in ways that already match
the code I gave below. You say to declare wbo Public in a standard module, which is what I did (didn't I? I meant to). And you say the function RangeValues doesn't know about wbo, but I already said I can see it in the Watch window as that statement is about to execute; doesn't that mean the function DOES know about it? I agree that wbo needs to be public; it just looks to me as though it is, and I don't know what else to think. --- "Dave Peterson" wrote: The function in the general module doesn't know about wbo. You could use: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub Then in the general module's function, make sure you refer to the correct variable: Option Explicit Function RangeValues(SheetName As String, RowA As Long, ColZ As Variant) _ As Variant Dim wso As Worksheet Set wso = Sheet1.wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function A couple of alternatives... #1. Move your public variables into a General module. Then they'll be able to be seen by any procedure in any module. #2. Pass the workbook as part of the function call. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wbo As Workbook Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues(wbo, "Map", 2, 4) End Sub and Option Explicit Function RangeValues(wbo As Workbook, SheetName As String, _ RowA As Long, ColZ As Variant) As Variant Dim wso As Worksheet Set wso = wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function (You could pass the workbook's name as a string if you wanted, too.) #3. Instead of passing strings to the function, pass the worksheet you want to use. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wks As Worksheet Set wks = Workbooks("MyWorkbook.xls").Worksheets("Map") Stns = RangeValues(wks, 2, 4) End Sub Option Explicit Function RangeValues(myWks As Worksheet, RowA As Long, ColZ As Variant) _ As Variant RangeValues = myWks.Cells(RowA, ColZ).Value End Function Bob Bridges wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
In private emails...
First, please keep the discussion in the newsgroup. It helps other people--even those who don't post. (That's the way I learned--by lurking in the newsgroups.) My qualifier for a general/standard module is kind of nebulous at best. I mean the one you get when you do Insert|Module. Next time you're stepping through your code with the watch window open, look at the Context column in that watch window. I think you'll see that wbo is local to that sheet module. And the ThisWorkbook and Sheet modules are private class (specially treated class) modules. I put this procedure in a worksheet module: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub The sheet that I used had a codename (not name that you see in the tab in excel) of Sheet1. That's why this line knew where to find wbo: Set wso = Sheet1.wbo.Worksheets(SheetName) If your sheet had a different codename, you'd have to change that line. It has nothing to do with the parent of the workbook. It's qualifying the variable--not the workbook. wbo.parent would be the excel application itself. -- Dave Peterson ----- Original Message ----- From: Bob Bridges To: <<snipped Sent: Monday, October 26, 2009 23:10 Subject: Switching to email: "The function in the general module doesn't know about wbo" Thanks for your help, Dave. Ok, let me go through this a piece at a time, because it sounds like you know some things I need to understand-but I don't understand, not yet: The function in the general module doesn't know about wbo. By the "general" module you mean the one I named "Common", right? (I keep shared routines in it, that's all.) But wait, I said below that when I'm just about to execute the "Set wso = wbo.Workbooks"-etc statement, I can see wbo in the Watch window. If (while executing RangeValues) I can see wbo in Watch, doesn't that prove that it does know about wbo? And because I defined wbo Public in the declarations of the sheet module, shouldn't wbo be "available to all procedures in the project", as the documentation says? You could use: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub Then in the general module's function, make sure you refer to the correct variable: Option Explicit Function RangeValues(SheetName As String, RowA As Long, ColZ As Variant) _ As Variant Dim wso As Worksheet Set wso = Sheet1.wbo.Worksheets(SheetName) Up until that last statement, that is what I used (right?). And that last one is screwy; I think you just made a typo or something there. What's Sheet1 and why do you think it can be a parent of the workbook? A couple of alternatives... #1. Move your public variables into a General module. Then they'll be able to be seen by any procedure in any module. What's a General module? The VB documentation says a "standard" module is one "containing only procedure, type, and data declarations and definitions". If that's what you meant, then by that definition I already do have wbo in a general module. (The other alternatives would work, and I'll use one if I have to. But of course I don't have to; the secret is to make wbo Public. I think that's what I've already done, according to both the documentation and your examples; I just can't figure out why it isn't working.) --- Bob Bridges, <snipped --- "Dave Peterson" wrote: The function in the general module doesn't know about wbo. You could use: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub Then in the general module's function, make sure you refer to the correct variable: Option Explicit Function RangeValues(SheetName As String, RowA As Long, ColZ As Variant) _ As Variant Dim wso As Worksheet Set wso = Sheet1.wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function A couple of alternatives... #1. Move your public variables into a General module. Then they'll be able to be seen by any procedure in any module. #2. Pass the workbook as part of the function call. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wbo As Workbook Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues(wbo, "Map", 2, 4) End Sub and Option Explicit Function RangeValues(wbo As Workbook, SheetName As String, _ RowA As Long, ColZ As Variant) As Variant Dim wso As Worksheet Set wso = wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function (You could pass the workbook's name as a string if you wanted, too.) #3. Instead of passing strings to the function, pass the worksheet you want to use. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wks As Worksheet Set wks = Workbooks("MyWorkbook.xls").Worksheets("Map") Stns = RangeValues(wks, 2, 4) End Sub Option Explicit Function RangeValues(myWks As Worksheet, RowA As Long, ColZ As Variant) _ As Variant RangeValues = myWks.Cells(RowA, ColZ).Value End Function --- Bob Bridges wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? Bob Bridges wrote: Seems to me you're telling me to change the progam in ways that already match the code I gave below. You say to declare wbo Public in a standard module, which is what I did (didn't I? I meant to). And you say the function RangeValues doesn't know about wbo, but I already said I can see it in the Watch window as that statement is about to execute; doesn't that mean the function DOES know about it? I agree that wbo needs to be public; it just looks to me as though it is, and I don't know what else to think. --- "Dave Peterson" wrote: The function in the general module doesn't know about wbo. You could use: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub Then in the general module's function, make sure you refer to the correct variable: Option Explicit Function RangeValues(SheetName As String, RowA As Long, ColZ As Variant) _ As Variant Dim wso As Worksheet Set wso = Sheet1.wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function A couple of alternatives... #1. Move your public variables into a General module. Then they'll be able to be seen by any procedure in any module. #2. Pass the workbook as part of the function call. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wbo As Workbook Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues(wbo, "Map", 2, 4) End Sub and Option Explicit Function RangeValues(wbo As Workbook, SheetName As String, _ RowA As Long, ColZ As Variant) As Variant Dim wso As Worksheet Set wso = wbo.Worksheets(SheetName) RangeValues = wso.Cells(RowA, ColZ).Value End Function (You could pass the workbook's name as a string if you wanted, too.) #3. Instead of passing strings to the function, pass the worksheet you want to use. Option Explicit Sub testme() Dim Stns As Variant '??? Dim wks As Worksheet Set wks = Workbooks("MyWorkbook.xls").Worksheets("Map") Stns = RangeValues(wks, 2, 4) End Sub Option Explicit Function RangeValues(myWks As Worksheet, RowA As Long, ColZ As Variant) _ As Variant RangeValues = myWks.Cells(RowA, ColZ).Value End Function Bob Bridges wrote: I'm still learning about scope and visibility of objects, but I thought I had a general handle on the concept. This problem, though, has me stonkered: In a sheet module, declarations section: Public wbo The same sheet module, the first procedure, the first two statements: Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) RangeValues is in a separate module named "Common". It starts out like this: Function RangeValues(SheetName, RowA, ColZ) Set wso = wbo.Worksheets(SheetName) So we enter RangeValues with the first argument set to "Map", and when it gets to the statement "Set wso=wbo.Worksheets(Sheet)" I expect it to set wso to the worksheet named "Map". Instead I get an error 91: Run-time error '91': Object variable or With block variable not set. This SEEMS to say that wbo isn't properly initialized as a workbook object. Yet just before it executes the Set statement I can see wbo in the Watch window, with the Map sheet as one of its properties. So is the error message talking about some other object? Can anyone see the obvious piece I'm missing? -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible scoping problem
That turned out to be the key: When I call RangeValues from a sheet module
the scope is different than when I call it from a standard module. And that bit about wbo being a property of the calling sheet is seriously weird, but I can use it, I think, to let the subroutine take calls from either type of module. Thanks, Dave, I think I'm back on track now. --- "Dave Peterson" wrote: Next time you're stepping through your code with the watch window open, look at the Context column in that watch window. I think you'll see that wbo is local to that sheet module....the ThisWorkbook and Sheet modules are private class (specially treated class) modules. I put this procedure in a worksheet module: Option Explicit Public wbo As Workbook Sub testme() Dim Stns As Variant '??? Set wbo = Workbooks("MyWorkbook.xls") Stns = RangeValues("Map", 2, 4) End Sub The sheet that I used had a codename (not name that you see in the tab in excel) of Sheet1. That's why this line knew where to find wbo: Set wso = Sheet1.wbo.Worksheets(SheetName) If your sheet had a different codename, you'd have to change that line. It has nothing to do with the parent of the workbook. It's qualifying the variable--not the workbook. wbo.parent would be the excel application itself. ----- Original Message ----- From: Bob Bridges Sent: Monday, October 26, 2009 23:10 The function in the general module doesn't know about wbo. But wait, I said below that when I'm just about to execute the "Set wso = wbo.Workbooks"-etc statement, I can see wbo in the Watch window. If (while executing RangeValues) I can see wbo in Watch, doesn't that prove that it does know about wbo? And because I defined wbo Public in the declarations of the sheet module, shouldn't wbo be "available to all procedures in the project", as the documentation says? You could use... Set wso = Sheet1.wbo.Worksheets(SheetName) I think you just made a typo or something there. What's Sheet1 and why do you think it can be a parent of the workbook? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
variable scoping - constants going out of scope - circular references | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
Question about scoping variables | Excel Programming |