Avatar of alisonthom
alisonthom
 asked on

Excel VBA - Use of Application.Caller within UDF

Hi,

I have writen UDF's that appeared to be working but now I have a problem.  The UDF's are are used in different rows and each UDF uses Application.Caller to determine the value of another cell in the same row.  Here is an example

x = Cells(Application.Caller.row, 4).Value

Other information I Have -
- The UDF's include the Application.Volatile statement.
- The function name has the following format:
    Function test(Optional default_value As Variant) As Variant

If I change a cell's value on another worksheet or in a different workbook I then see multiple instances of #VALUE! in cells that have a UDF.

I put a break in the code and when the function has been called (after changing the cell in another worksheet or workbook) the evaluated value of x is 'empty'.  

I would be very grateful if someone could please help me.

Many thanks in advance
Alison
Microsoft Excel

Avatar of undefined
Last Comment
alisonthom

8/22/2022 - Mon
Steve

It is not good practice to have relative references in a UDF...

So if the UDF needs data from columns 1 and 2 to fill column 3 you would need:

=UDF(FirstCell,SecondCell)  or =UDF(FirstCell, LookupRange)

so you have all the variables entered into the UDF at the start.
calling relative caller - 1.row makes for unexpected and troublesome UDF.

Do you wish to post the full UDF and we can suggest improvements?
Rory Archibald

I agree totally with The_Barman. If for some reason you have to use this approach, you must specify the sheet for Cells:

x = Application.caller.worksheet.Cells(Application.Caller.row, 4).Value
alisonthom

ASKER
Thank you both for your quick and very helpful responses.

I have modified the call to the UDF by using Row() to derive the row number which is then passed to the UDF.  I then removed the Application.Caller.row from the UDF and x is then derived as
    x = Cells(Row, 4).Value

When I change the value of a cell in another workbook that is open the #VALUE! appears and x has a value of 'empty', presmably because the UDF is referencing the other workbook.  

Would it be acceptable to use the following (which does appear to work)?  Incidentally, the name of the workbook with the UDF's is not fixed.

x = Application.Caller.Worksheet.Cells(Row,4).Value

Many thanks
Alison
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Rory Archibald

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Steve

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alisonthom

ASKER
Thank you both again.

Initially I did include arguments for the other cells but I have other UDF's with more arguments, the greatest being 7.  From the user's perspective it looked more compact to have a UDF with no arguments rather than 7.  

I am willing to apply this approach (with an argument passed for each cell) if that is the best and right way.  

Many thanks
Alison
Rory Archibald

I would probably have a serious rethink about a UDF that needs that many arguments.
Steve

The use of 7 arguments can be excessive, maybe the use of a Range or ParamArray would tidy the function up. Array handling and off sheet processing can really speed up the function.

Please feel free to post a version of the function here and I am sure we can provide guidance to improve things for you.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alisonthom

ASKER
Many thanks The_Barman for the offer.

Here is a copy of the function with 7 arguments.

Function Xval(StartDate As Date, EndDate As Date, Liability As Long, DDate As Date, Instal1 As Long, Instal2 As Long, InstalYN As Integer, _
             Optional default_value As Variant) As Variant
Application.Volatile

Dim ToAlloc As Long
Dim Plength, WholeMths, spare_days, n As Integer

    Plength = EndDate - StartDate + 1
   
    If InstalYN = 1 And IsDate(DDate) Then
        WholeMths = WorksheetFunction.RoundDown(Plength / 30, 0)
       
        spare_days = EndDate - DateAdd("m", WholeMths, StartDate) + 1
        n = WholeMths + Round(spare_days / 30, 2)
        ToAlloc = Liability - Instal1 - Instal2
   
        If Plength = 365 Or Plength = 366 Then
            Xval = 3 * Liability / n
        Else
            If (3 * Liability / n) < ToAlloc Then
                Xval = 3 * Liability / n
            Else
                Xval = ToAlloc
            End If
        End If
    Else
        Xval = 0
    End If
   
End Function

Thanks again, Alison
Steve

Erm, sorry to be a pain but would you be able to provide a sample worksheet too, just with 3-4 lines of random data.

I am not sure if you want to dim Plength as a variant but it is a common misconception (one I had until I started on this site) that you can dim with commas and just define at the end.

So:
Dim Plength, WholeMths, spare_days, n As Integer
is in effect:
Dim Plength as variant, WholeMths as variant, spare_days as variant, n As Integer
so should be:
Dim Plength As Integer, WholeMths As Integer, spare_days As Integer, n As Integer

As all dependencies are passed to the function you can drop the Application.Volatile too.
The function as is will calculate on change with the volatile, or only on change of dependency without it.

the Plength = End - Start only comes into effect inside the If = true (so moving it inside will make a slight diference)

If InstalYN = 1 And IsDate(DDate) Then
        'the following calc only comes into effect inside If, so move it inside
        Plength = EndDate - StartDate + 1


Or you can drop the Plength all together with (eliminating a variable)
WholeMths = WorksheetFunction.RoundDown((EndDate - StartDate + 1) / 30, 0)

I will see if there are any other changes with the sample data.
Steve.
alisonthom

ASKER
Thanks for the comments and suggestions Steve.  

Attached is a new workbook which just contains the relevant cells together with 4 rows of data.  

The UDF is used in cells W9 - W12.  Similar UDF's will be used in other columns but I have not included them in this workbook.

Thanks, Alison
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve

Hi there Alison, missing attachment.
alisonthom

ASKER
I am sorry Steve.  Not sure why that failed.  I will try again
Function-example.xlsm
Steve

A question:
you dim n as integer before using the Round on Spare_days:
n = WholeMths + Round(spare_days / 30, 2)
but as Spare_days will always be less than 30 and n will force no decimals (integer) the round to 2 decimals will have the effect of a Round to 0 decimals. Is this intentional?

Elimination of Worksheet function:
WholeMths can be calculated with the DateDiff function:
This will be more accurate...
WholeMths = DateDiff("m", StartDate, EndDate + 1)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alisonthom

ASKER
Thanks for this Steve!
n should be declared as long.

Also, thank you for the alternative derivation of WholeMths.  That is better.

Alison
Steve

Hi there Alison, you are most welcome...

n would still round to no decimal places if it is Long.

To have n to 2 decimal places it should be Double data type.

ATB
Steve.
alisonthom

ASKER
thanks for all your help Steve.  

Is there anything I can do instead of using 7 arguments with the UDF?

Thanks, Alison
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Steve

The use of 7 arguments seems to be nescessary.
(though the optional default is not used in the code)

But it is far far better to pass the arguments into the function this way rather than cell offset.

There are little changes that you could still make, but the function is good as it is.

ATB
Steve.
alisonthom

ASKER
many thanks again Steve for all your help.  It really is most appreciated.

Thanks, Alison