User Defined Function Problem
 

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

User Defined Function Problem
 

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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

Hi Bernie,

With all cells selected, try:

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

---
Regards,
Norman



"bw" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

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

"Norman Jones" wrote in message
...
Hi Bernie,

With all cells selected, try:

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

---
Regards,
Norman



"bw" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

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

--
Regards,
Tom Ogilvy




"bw" wrote in message
...
Norman,
No change. Thanks for the suggestion. I'll try anything...
Bernie

"Norman Jones" wrote in message
...
Hi Bernie,

With all cells selected, try:

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

---
Regards,
Norman



"bw" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

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" wrote in message
...
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

--
Regards,
Tom Ogilvy




"bw" wrote in message
...
Norman,
No change. Thanks for the suggestion. I'll try anything...
Bernie

"Norman Jones" wrote in message
...
Hi Bernie,

With all cells selected, try:

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

---
Regards,
Norman



"bw" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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

User Defined Function Problem
 

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

--
Regards,
Tom Ogilvy

"bw" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy




"bw" wrote in message
...
Norman,
No change. Thanks for the suggestion. I'll try anything...
Bernie

"Norman Jones" wrote in message
...
Hi Bernie,

With all cells selected, try:

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

---
Regards,
Norman



"bw" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman


"bw" wrote in message
...
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