Possible scoping problem

[Bob Bridges]

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?

 

[Barb Reinhardt]

I think I'd change

Function RangeValues(SheetName, RowA, ColZ)

To

Function RangeValues(SheetName as string, RowA as long, ColZ as long)

 

[Barb Reinhardt]

I'd also add

Dim WSO as excel.worksheet 'For early binding
Dim WSO as object 'For late binding

 

[Bob Bridges]

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)

 

[Bob Bridges]

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.

 

[Dave Peterson]

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]

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]

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>

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

Set wbo = Workbooks("MyWorkbook.xls")
Stns = RangeValues("Map", 2, 4)Function RangeValues(SheetName, RowA, ColZ)
Set wso = wbo.Worksheets(SheetName)

 

[Bob Bridges]

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.