Function argument's cell adress

[John99]

Hi all!

I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be

RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)

Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3

And here is the code I have tried to produce:

Please Login or Register  to view this content.

The problem in the function is that I (of course) cannot use "Amount.Offset" or "CallProb.Offset". So how could I determine what are the cell adresses for those arguments? So if the RollingTime argument is "2", Amount.offset(0,-2).Value would be 1587172158.

Hope I am not too confusing..

-John

[DonkeyOte]

What is CallProb meant to reflect ? You are not passing a Variable of that name - so presumably Percentage ?

As a general rule of thumb when using UDFs - whenever the values being passed originate from cells pass the variables as Ranges and not as values/data types as this gives you far greater flexibility in the long-run, eg instead of:

Please Login or Register  to view this content.

which would fail (Offset as per your own function) you would use

Please Login or Register  to view this content.

ie Range objects give you far more flexibility ...

Going back to your actual question... so this is a recursive calc ?

ie Previous should really be a cumulative total, ie

Please Login or Register  to view this content.

Correct ?

[DonkeyOte]

Based on your original post and:

RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)

Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3

I wonder if the below will work for you ?

Please Login or Register  to view this content.

Called from cell as:

Please Login or Register  to view this content.

For Rolling Time of 2 (ie D9) the above would generate 619951097.3 which I believe ties out to your initial post.

[John99]

DonkeyOte,

Thank you for your answer! The CallProb was actually supposed to be Percentage. I have been changing names and I forgot to change that one.

Range object is really the way to go here and the function works nicely.

This might be a stupid question but if I am changing the Percentage for the RollingTime = 2, where can i define that it recalcutes the TOTALCALLS values for RollingTimes =3,4,5..? Now i have to copy across the cells every time I am changing the Percentage argument..¨

[DonkeyOte]

This might be a stupid question but if I am changing the Percentage for the RollingTime = 2, where can i define that it recalcutes the TOTALCALLS values for RollingTimes =3,4,5..? Now i have to copy across the cells every time I am changing the Percentage argument.

Not at all, this is a key point when using UDFs, you want to avoid the requirement of Application.Volatile given UDFs are generally slower than native Functions, making UDFs Volatile is generally bad news.

The answer is to perhaps alter the approach such that you pass the "cumulative ranges" so to speak, eg:

Please Login or Register  to view this content.

Using your original file:

Please Login or Register  to view this content.

As you alter the % you should find your results adjust accordingly given you're now explicitly setting a dependency in the Function to those % cells, ie altering the % in say D8 would cause all functions from D onwards to recalculate given explicitly referencing D8 in the Function call.

[John99]

DonkeyOte,

Thanks so much for your code. Cumulative ranges seems to be doing a good job!

Br,
John

[John99]

Hi!

My function is working nicely when I have argument ranges horizontally. But if I am trying to use vertical data I cannot get it work. So I have to modify the following part of my code.

Please Login or Register  to view this content.

Any ideas what I have to do? I struggled quite awhile now but cannot get it work..

rngPct(1 + lngRT).Value and rngAmt(1 + lngRT).Value are the ones i should be modifying..

Thanks for any help!

-Jack

Please Login or Register  to view this content.

[DonkeyOte]

Jack - could you post a sample file with the horizontal ranges in play ? Hard to visualise (we coded this a while back of course)

Just a quick note re: your code - the Function is called VTOTALCALLS but you're using HTOTALCALLS in the UDF meaning the output will not be quite as intended...

[John99]

DonkeyOte,

Thanks for your reply. I have attached an example of a horizontal version and it's working nicely.

The HTOTALCALLs in my UDF should be VTOTALCALLs.. I have just been playing with the names...

[John99]

Hi! here is the attachment with a VTOTALCALLS example.. just missing the values of the function..

[DonkeyOte]

By my reckoning the existing function will work equally well in either direction, all that needs to alter are the range references when calling from the cell

Horizontal:
C7: =TOTALCALLS(C$4,$C$6:C$6,$C$5:C$5)
copied across to P=7

Vertical
E12: =TOTALCALLS($B12,$D$12:$D12,$C$12:$C12)
copied down to E25