User Defined Function Problem

[bw]

I have a user defined function that is working quite well. This function
refers to data in it's own workbook. But the workbook is updated when
reading in a delimited txt file.

Everything updates properly as it should, except the cells using the USD.
Pressing F9 updates these cells only sometimes, but I haven't been able to
figure out when.

The sure way to update them is to press F2 in each cell, and then press
enter.

Can someone explain why these cells are not updating automatically?

Thanks,
Bernie

 

[Norman Jones]

Hi Bernie,

Hi Bernie,

Adding:

Application.Volatile

at the head of the function, should allow all of the function cells to
recalculate when the sheet is recalculated.

 

[bw]

Thanks for the reply Norman,

"Application.Volatile" didn't help. I watch as the program steps through
the function, and everything seems to be okay. But when it has finished,
the data is unchanged.

Keep in mind, that the cells will be updated when I press <F2><Enter>, in
that specific USD cell.

The workbook that is created when I read in my data, and which is the object
of the USD is still open. When I physically change the data in that
workbook, I can see values change, even in the USD cells, but the value is
not correct.

When I run my macro to get the comma delimited file, I get a msgbox that
reads "This workbook contains links to other data services". Could this
problem have something to do with these links? I have answered "Update" and
"Don't Update" with no change in the calculations of the USD.

When I try to record the keystrokes <F2><Enter> I get a message from Excel
"Unable to Record". Bummer!

I'm still looking for a fix...

Thanks,
Bernie

 

[Norman Jones]

Hi Bernie,

With all cells selected, try:

Edit|Replace
what: = (equal sign)
with: = (equal sign)
Replace all

 

[bw]

Norman,
No change. Thanks for the suggestion. I'll try anything...
Bernie

 

[Tom Ogilvy]

the only other thing to try would be

for each cell in ActiveSheet.Cells.specialCells(xlformulas)
if instr(1,cell.Formula,"myUSD",vbTextCompare) then
cell.Formula = Cell.Value
end if
Next


in xl2000 and later you can try

Application.CalculateFull

or
in xl 97 or later

Ctrl+Alt+F9

 

[bw]

Hooray!

I used "Application.CalculateFull" and it now works!
This seems pretty strange to me. Are you able to suggest why this is
necessary?
I really appreciate your help Tom.

Thanks much,
Bernie

 

[Tom Ogilvy]

Based on your other reports, I can not say why it might be necessary.