Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
I have a spreadsheet that uses some user defined functions.
I have included in the code application.volitile, so they recalc when the workbook needs calculating. They are public functions and are located in module1. All seems to work fine, except ... if I open another spreadsheet or do a calc in another spreadsheet, when I return to the workbook containing the UDFs, all of the UDF cells show #VALUE. If I hit the calc button, the UDFs all calculate. Question: How can I get the UDFs to calculate when I have other workbooks open at the same time? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
Sounds like a problem in the UDF. Post it.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "makulski" wrote in message ... I have a spreadsheet that uses some user defined functions. I have included in the code application.volitile, so they recalc when the workbook needs calculating. They are public functions and are located in module1. All seems to work fine, except ... if I open another spreadsheet or do a calc in another spreadsheet, when I return to the workbook containing the UDFs, all of the UDF cells show #VALUE. If I hit the calc button, the UDFs all calculate. Question: How can I get the UDFs to calculate when I have other workbooks open at the same time? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
Well rather than post my UDF, which is rather elaborate, I tried to recreate
the problem with a simpler UDF. My first attempt didn't exhibit the problem described. public function addone(InputValue As Double) As Double Application.Volatile addone = InputValue + 1 End Function That worked fine. So I made it closer to my UDF by adding a Application.Worksheet function. Create a named range called TestRange 2 columns by 3 rows. Put values in it like this: Five 5 Six 6 Seven 7 Now, create this function: Public Function addsome(InputValue As Double, AddWhat As String) As Double Application.Volatile Dim ToAdd As Double ToAdd = Application.WorksheetFunction.VLookup(AddWhat, Range("TestRange"), 2, False) addsome = InputValue + ToAdd End Function If you put thius into a worksheet cell =addsome(A1,"Five") Calc it. If A1 contains 1, the result will be 6. Go to another workbook, hit calc. Now return to the UDF workbook, and the result is #VALUE Hit calc and it resumes being 6. I hope that is more or less complete. So it looks like my problem is the way I am using Worksheetfunction or Range() Any corrections you see I need to make. Thanks. "Bob Phillips" wrote: Sounds like a problem in the UDF. Post it. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
Try this
Public Function addsome(InputValue As Double, AddWhat As String) As Double Application.Volatile Dim ToAdd As Double ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _ Application.Caller.Parent.Range("TestRange"), 2, False) addsome = InputValue + ToAdd End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "makulski" wrote in message ... Well rather than post my UDF, which is rather elaborate, I tried to recreate the problem with a simpler UDF. My first attempt didn't exhibit the problem described. public function addone(InputValue As Double) As Double Application.Volatile addone = InputValue + 1 End Function That worked fine. So I made it closer to my UDF by adding a Application.Worksheet function. Create a named range called TestRange 2 columns by 3 rows. Put values in it like this: Five 5 Six 6 Seven 7 Now, create this function: Public Function addsome(InputValue As Double, AddWhat As String) As Double Application.Volatile Dim ToAdd As Double ToAdd = Application.WorksheetFunction.VLookup(AddWhat, Range("TestRange"), 2, False) addsome = InputValue + ToAdd End Function If you put thius into a worksheet cell =addsome(A1,"Five") Calc it. If A1 contains 1, the result will be 6. Go to another workbook, hit calc. Now return to the UDF workbook, and the result is #VALUE Hit calc and it resumes being 6. I hope that is more or less complete. So it looks like my problem is the way I am using Worksheetfunction or Range() Any corrections you see I need to make. Thanks. "Bob Phillips" wrote: Sounds like a problem in the UDF. Post it. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
That's the secret incantation!
It works perfectly. Thanks so much. "Bob Phillips" wrote: Try this Application.Caller.Parent.Range("TestRange") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
I'm back. That worked for my addsome function. But when I tried to apply it
to my real function, it didn't work and it occurs to me that I don't understand the soln anyway. I thought I'd just stick Application.Caller.Parent in front of every range() in my function. But that gives me an error 424 Object required. application.caller seems to return a large integer (no idea what it is) application.caller.parent (or dot any other property gets a 424). It seems to work in the first worksheetfunction.(,range(),) though I don't know why, but the next occurance in the UDF causes it to drop out of runtime. ?? Next idea? "Bob Phillips" wrote: ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _ Application.Caller.Parent.Range("TestRange"), 2, False) addsome = InputValue + ToAdd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
It's hard to say without seeing the code. Your problem was caused because it
was trying to refer to the range of the activesheet, so I forced it refer to the range's parent sheet. Post the whole code. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "makulski" wrote in message ... I'm back. That worked for my addsome function. But when I tried to apply it to my real function, it didn't work and it occurs to me that I don't understand the soln anyway. I thought I'd just stick Application.Caller.Parent in front of every range() in my function. But that gives me an error 424 Object required. application.caller seems to return a large integer (no idea what it is) application.caller.parent (or dot any other property gets a 424). It seems to work in the first worksheetfunction.(,range(),) though I don't know why, but the next occurance in the UDF causes it to drop out of runtime. ?? Next idea? "Bob Phillips" wrote: ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _ Application.Caller.Parent.Range("TestRange"), 2, False) addsome = InputValue + ToAdd |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
Yes, after some debugging, the references to ranges are getting confused when
they are referenced from another active workbook. I'm having some success in getting this to work. I replaced references in the UDF that looked like this: range(MyColLookup) with this: MyWorkbook.Names(MyColLookup).RefersToRange This actually works, but now I have a chicken-and-egg problem. In my function, I have hardcoded the name of my workbook: Dim MyWorkbook As Workbook Set MyWorkbook = Application.Workbooks("Quik3.xls") How can I have the UDF determine the name of the workbook it is in without my actually having to type it into the code? I see in the Project explorer the module is under VBAProject(Quik3.xls) Can the code reference its own parent? (By the way, thanks for the quick replies to my questions. I hope somebody is paying you for this attention :-) "Bob Phillips" wrote: It's hard to say without seeing the code. Your problem was caused because it was trying to refer to the range of the activesheet, so I forced it refer to the range's parent sheet. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
"makulski" wrote in message ... Yes, after some debugging, the references to ranges are getting confused when they are referenced from another active workbook. I'm having some success in getting this to work. I replaced references in the UDF that looked like this: range(MyColLookup) with this: MyWorkbook.Names(MyColLookup).RefersToRange This actually works, but now I have a chicken-and-egg problem. In my function, I have hardcoded the name of my workbook: Dim MyWorkbook As Workbook Set MyWorkbook = Application.Workbooks("Quik3.xls") This shouldn't be necessary. The way to do it is to tie the range back to its parent sheet, as I showed you, which automatically ties it back to the workbook. How can I have the UDF determine the name of the workbook it is in without my actually having to type it into the code? I see in the Project explorer the module is under VBAProject(Quik3.xls) Can the code reference its own parent? Yes it can, it is called ThisWorkbook. But as I said, it ain't necessary. (By the way, thanks for the quick replies to my questions. I hope somebody is paying you for this attention :-) Nope, we do it gratis. Anyway, I am off to bed, so you won't see me again until tomorrow now. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
UDF - not always calculated
OK, here it is (abridged to the relevant bits)
Public Function Switchboard(MyLookup As String, MyRow As String, MyCol As String, Optional MyReturn As String = "Value") As Variant Application.Volatile Dim MasterSwitch As Range, MyRowLookup As String, MyRowRange As Range '(1) Set MasterSwitch = Application.Caller.Parent.Range("MasterSwitch") If MasterSwitch.Find(MyLookup) Is Nothing Then Switchboard = MyLookup & " is not a valid argument" Exit Function End If MyRowLookup = Application.WorksheetFunction.VLookup(MyLookup, MasterSwitch, 5, False) '(2) Set MyRowRange = Application.Caller.Parent.Range(MyRowLookup) Your trick works fine when I use it at (1). But when I try and use it again at (2), I get a 424 error and the function drops out. "Bob Phillips" wrote: This shouldn't be necessary. The way to do it is to tie the range back to its parent sheet, as I showed you, which automatically ties it back to the workbook. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Calculated Items on Calculated Fields. | Excel Worksheet Functions | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |