Solved

Excel VBA - Use of Application.Caller within UDF

Posted on 2013-01-08
19
1,375 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:alisonthom
  • 8
  • 8
  • 3
19 Comments
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
0
 

Author Comment

by:alisonthom
Comment Utility
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
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
Comment Utility
It depends on how you define acceptable ;)

To me, no - I think you should always explicitly pass all required ranges as arguments to the udf. What is your reason for not doing so?
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 125 total points
Comment Utility
Again me and Rory are in agreement here... try to pass all variables to the udf rather than picking values from "other cells".

If you are cycling through cells then it is always faster to fill an array then handle it... so passing all data into the function is best (try and think of an in-built function which uses non-referenced cells to get it's results, I can't think of one)
0
 

Author Comment

by:alisonthom
Comment Utility
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I would probably have a serious rethink about a UDF that needs that many arguments.
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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.
0
 

Author Comment

by:alisonthom
Comment Utility
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 24

Expert Comment

by:Steve
Comment Utility
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.
0
 

Author Comment

by:alisonthom
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
Hi there Alison, missing attachment.
0
 

Author Comment

by:alisonthom
Comment Utility
I am sorry Steve.  Not sure why that failed.  I will try again
Function-example.xlsm
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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)
0
 

Author Comment

by:alisonthom
Comment Utility
Thanks for this Steve!
n should be declared as long.

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

Alison
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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.
0
 

Author Comment

by:alisonthom
Comment Utility
thanks for all your help Steve.  

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

Thanks, Alison
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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.
0
 

Author Comment

by:alisonthom
Comment Utility
many thanks again Steve for all your help.  It really is most appreciated.

Thanks, Alison
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now