function only runs once

[mygmat123]

I have a function that I've written:
getpercent(variable1,variable2)

When I try to use the function, it seems to run once, but then uses the same return value for all other cells

cell A1= =getpercent(1,2) returns "0.03"
which is correct..

but if I use it in another cell

cell A3= =getpercent(2,3) return "0.03"
which is not correct it should return "0.05.

It seems like once excel runs the function once, it just uses the same value over and over again. How can I fix this?
I need the function to run once the cell losses focus, it this possible, and what am I doing wrong?

 

[TonyJollans]

Hi mygmat123,

The function should run for each cell.

Are you sure the function would produce what you say is the right result? Can you post your code.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[PHV]

Add this code as the first line of your function body:
Application.Volatile

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mygmat123]

TonyJollans- I'm sure my code calculation are correct. The issue is the excel seems to call the function once, and you that returned value for all other cells I use the function. It does not even take into account that the variables are different.

It's very weird.

PHV- I tried your solution, but it still does not work.
Now,when the function is called, it changes all cells with the function to that same value of the cell that just ran. Where before, each cell only got the returned value when the cell lost focus.

It seems like excel just does not want to re-run the function with different variables.

Any other ideas?





I actually read

 

[PHV]

The parameters of the function must be cell address instead of hardcoded values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Application.Volatile forces recalculation every interaction instead of just when something Excel considers important changes.

Your response to that means that I am not now following at all. You have your function in lots of cells - with different results, but when they are recalculated they suddenly all give the same result. How did you get the different results in the first place give the problem you have?

What about setting a break in your function and watching what happens. You'll be able to step through it whether it runs once or many times - and know for sure what is happening.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[mygmat123]

I just tested a few things and this is what I found.

If I make a new function:

Public Function test(a As Variant, b As Variant)
test = (a + b) / 100
End Function

It works fine.

But my needed function does not.

I can not post the code for security reasons, but it basically makes a connection to a server, downloads a file, then fills an array with the data. the function then uses that array information to calculate a number.

That number is returned by the function.

PS. my functions includes one "string", and one optional "date" variable.

PLEASE HELP!

thanks

 

[mygmat123]

I'll try a cell reference

 

[mygmat123]

the cell reference does not make it work either.

I'll try to discribe my problem again, so you can better understand.

When I input the function in a cell, excel calls it and returns a value(lets say the number 5).

Now if I type the function with different variables in any other cell, after the intial cell. the value 5 is the only think that appears.


I appreciate the assistance!
thanks

 

[TonyJollans]

Hi mygmat123,

If other functions work correctly and this one does not, then it seems fairly clear that the error is in the function. Are you perhaps using global variables that are not being reset, or some similar thing? Have you tried single stepping through the code and watching your values?

It is perfectly alright to use hardcoded values in function calls but, given what you say the function does, you probably do not want application.volatile for performance reasons.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[mygmat123]

yes, I tried stepping through it, but it only runs once during the intial cell, but never runs again. The intial value seems to be stored in excel and it returns that value without running the function again.

 

[mygmat123]

Thanks for the help. I have one component added in, which I make an object on the global level. But when I moved to to the function level. It works all the time.

THanks, IT WORKS! GREAT!

 

[xlbo]

tip for posting here - post the code. full stop. If you want to hide server / security data then go for it - 99% of the time the server path etc will have no bearing on the problem at all so I don't understand why you couln't post the relevent code

anyway - glad your issue got sorted but it probably could've been solved much quicker if you had posted the code

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question