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?
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 said:
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)