Functions not Calculating

[Guest]

I have a "fairly" complex spreadsheet with about 10 worksheets, each with
about 700 rows of info. On two of them, I have created my own user defined
function for some complex calculations to simply the forumula.

The problem is that it is not calculating. I do have calculation set to
Automatic in my options, an if I select the cell, and press F2, enter, it
seems to update. Occasionally, on it's own, it also starts calculating when
I have not touch any of the fields with the formulas in them. F9 does not
make it calculate, either.

With all this data, I really cannot afford to keep rechecking to see if it
re-calculated...it would skew my entire model.

Anyone know why this behavior occurs? I have read many threads on this, and
none of the suggestions work to make it "automatically" calculate.

Thanks!

pat

 

[Jim Cone]

One thing that is often overlooked is using values from worksheet cells
that are not in the function argument list. An example of this could be...

Function EasyMultiply(By Ref Num1 as Double)
EasyMultiply = Num1 * ActiveSheet.Range("B5").Value
End Function

Charles Williams has lots of information here...
http://www.decisionmodels.com/calcsecretsj.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"PatK"
<[email protected]>
wrote in message
have a "fairly" complex spreadsheet with about 10 worksheets, each with
about 700 rows of info. On two of them, I have created my own user defined
function for some complex calculations to simply the forumula.

The problem is that it is not calculating. I do have calculation set to
Automatic in my options, an if I select the cell, and press F2, enter, it
seems to update. Occasionally, on it's own, it also starts calculating when
I have not touch any of the fields with the formulas in them. F9 does not
make it calculate, either.

With all this data, I really cannot afford to keep rechecking to see if it
re-calculated...it would skew my entire model.

Anyone know why this behavior occurs? I have read many threads on this, and
none of the suggestions work to make it "automatically" calculate.
Thanks!
pat

 

[Guest]

Interesting. I am passing values to the function, and one of those values is
performing a vlookup against another "fixed" table in the file (ie, I point
to it "directly" from the function). So, I guess I am doing that, but then,
what is the "fix" or alternative? (ie, "don't do that ?

 

[Jim Cone]

worksheet formula...
=easymultiply(B2,VLOOKUP("delta",A5:C8,3,0))

Function EasyMultiply(ByRef Num1 As Double, ByRef Num2 As Double)
EasyMultiply = Num1 * Num2
End Function
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"PatK"
<[email protected]>
wrote in message
Interesting. I am passing values to the function, and one of those values is
performing a vlookup against another "fixed" table in the file (ie, I point
to it "directly" from the function). So, I guess I am doing that, but then,
what is the "fix" or alternative? (ie, "don't do that ?

 

[Guest]

I can see how this would work. The problem I have, and the reason I created
the UDF for in the first place, is that I am actually doing 7 vlookups inside
the function. My goal was to make the original cell formula easier to work
with, passing just a few lookup values, performing some calculations, and
returning the result. Guess I might be "stuck" in this regard. Not really
understanding, tho, why it is not calculating or what affect doing it the way
I am, is having on that calculation. In fact, until the spreadsheet got to
big, all the calcs were working fine. Anyway...I guess I will wing it.

I was curious, tho (and this is a real noob question)...when would you pass
byRef, versus ByVal? What are the benefits of doing either? Thanks! (I am
passing byVal).

Pat

 

[Jim Cone]

Strongly suggest that you read the information at Charles Williams website.
See my original post.

As for ByRef vs. ByVal (ByRef is the default) ...
On a practical level, if arguments are passed ByRef, then any changes to
the variable are passed back to the calling sub or function.
Passing variables ByVal creates a copy of the variable.
Any changes to the variable are not passed back to the calling sub or function.

Passing arguments ByRef in most case is faster.
Some claim that passing Integers and Longs ByVal is faster.
However, I doubt in either case if one could measure the difference.

Jim Cone



"PatK"
<[email protected]>
wrote in message
I can see how this would work. The problem I have, and the reason I created
the UDF for in the first place, is that I am actually doing 7 vlookups inside
the function. My goal was to make the original cell formula easier to work
with, passing just a few lookup values, performing some calculations, and
returning the result. Guess I might be "stuck" in this regard. Not really
understanding, tho, why it is not calculating or what affect doing it the way
I am, is having on that calculation. In fact, until the spreadsheet got to
big, all the calcs were working fine. Anyway...I guess I will wing it.

I was curious, tho (and this is a real noob question)...when would you pass
byRef, versus ByVal? What are the benefits of doing either? Thanks! (I am
passing byVal).
Pat

 

[Guest]

Thank you!!!

Strongly suggest that you read the information at Charles Williams website.
See my original post.

As for ByRef vs. ByVal (ByRef is the default) ...
On a practical level, if arguments are passed ByRef, then any changes to
the variable are passed back to the calling sub or function.
Passing variables ByVal creates a copy of the variable.
Any changes to the variable are not passed back to the calling sub or function.

Passing arguments ByRef in most case is faster.
Some claim that passing Integers and Longs ByVal is faster.
However, I doubt in either case if one could measure the difference.

Jim Cone



"PatK"
<[email protected]>
wrote in message
I can see how this would work. The problem I have, and the reason I created
the UDF for in the first place, is that I am actually doing 7 vlookups inside
the function. My goal was to make the original cell formula easier to work
with, passing just a few lookup values, performing some calculations, and
returning the result. Guess I might be "stuck" in this regard. Not really
understanding, tho, why it is not calculating or what affect doing it the way
I am, is having on that calculation. In fact, until the spreadsheet got to
big, all the calcs were working fine. Anyway...I guess I will wing it.

I was curious, tho (and this is a real noob question)...when would you pass
byRef, versus ByVal? What are the benefits of doing either? Thanks! (I am
passing byVal).
Pat