Keeping function calc within its own sheet
Posted by Mike W. on February 11, 2002 5:47 PM
OK - here's a simplified version of my prob:
3 sheets - in:
A1 = 6 on sheet1
A2 = 7 on sheet2
A3 = 8 on sheet3
on each sheet I call the function myTest()
Public Function myTest()
myTest = Cells(1, 1)
End Function
-or-
myTest = range("A1")
When I force the calculation, the function returns depend on which page I'm currently calc'ing. Example- I'm on sheet3, F9 it, and each page's function returns 8.
What am I missing? I figured each time a sheet called it's own function it would use it's own sheet's info...
Posted by Juan Pablo G. on February 11, 2002 6:41 PM
Mark, one thing, VBA assumes some things, to "help" the use avoid as much typing as it possibly can, so, for instance, you don't have to put the Application in front of everything, for example, you don't have to use:
Application.ThisWorkbook
just use
ThisWorkbook
Same thing happens in the 'Workbook' level, you don't have to put
ThisWorkbook.Sheets("Sheet1")
just use
Sheets("Sheet1")
(This, of course is the general, it is needed in some cases)
and same goes in the 'Sheet' level. So, for your question, you're using Cells(1,1), VBA it's assuming it's the ActiveSheet, and it's returing the value to wherever it is being called.
If you want, you could use this version of your function (I know it was an example, but maybe it will help.
Function MyTest()
MyTest = Application.Caller.Parent.Cells(1, 1)
End Function
I still would suggest that you would EXPLICITLY put the Sheet's name as an argument, so you could use it like this:
Function MyTest(ShName as String)
MyTest = Sheets(ShName).Cells(1,1)
End Function
Anyway, hope that helps
Juan Pablo G.
Posted by Mike W. on February 11, 2002 8:10 PM
Exactly what I was looking for - Thanks Juan!! *NT*
*NT*
