Solved

Excel VBA - Use of Application.Caller within UDF

Posted on 2013-01-08
19
1,390 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
ID: 38755295
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
ID: 38755325
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
ID: 38755771
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
ID: 38755825
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
ID: 38755846
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
ID: 38755931
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
ID: 38756349
I would probably have a serious rethink about a UDF that needs that many arguments.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38756350
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
ID: 38756570
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Steve
ID: 38756682
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
ID: 38756892
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
ID: 38757900
Hi there Alison, missing attachment.
0
 

Author Comment

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

Expert Comment

by:Steve
ID: 38758360
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
ID: 38758519
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
ID: 38758591
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
ID: 38759416
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
ID: 38759793
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
ID: 38759850
many thanks again Steve for all your help.  It really is most appreciated.

Thanks, Alison
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro to determine Text Replacement 17 38
How to Calculate Stainless Steel U-Bolt Dimension 15 66
Excel Formula 4 28
FormulaArray VBA Issue 6 17
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

914 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

19 Experts available now in Live!

Get 1:1 Help Now