UDF - not always calculated

[makulski]

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?

 

[Bob Phillips]

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]

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]

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]

That's the secret incantation!
It works perfectly.
Thanks so much.

 

[makulski]

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?


:

ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _

 

[Bob Phillips]

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]

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]

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.

 

[makulski]

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.