Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Cells that Get Value from Macro
Hi.
I've written a VB macro that references a cell by name via the Range function. I call the macro from various cells in my spreadsheet. The value the macro returns becomes the value of the cell. I'd like the cells that use the macro to automatically update when the referenced cell value is changed. This is not happening. What do I need to do to get that to work? If it can't be done, is there a manual way to cause all cells on the worksheet to get refreshed all at once? As it stands, the only way I can update the cells is to go through them manually, one at a time, and click inside the formula bar for each one. I'm sure there must be a better way. Thanks for the better way :), Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Cells that Get Value from Macro
Have you macro put in a formula rather than a value.
Assume E9 is the cell they reference. In the cell where the value is place, the macro should put the formula =E9 rather than the value displayed in E9. As an example. -- Regards, Tom Ogilvy "Ken" wrote in message om... Hi. I've written a VB macro that references a cell by name via the Range function. I call the macro from various cells in my spreadsheet. The value the macro returns becomes the value of the cell. I'd like the cells that use the macro to automatically update when the referenced cell value is changed. This is not happening. What do I need to do to get that to work? If it can't be done, is there a manual way to cause all cells on the worksheet to get refreshed all at once? As it stands, the only way I can update the cells is to go through them manually, one at a time, and click inside the formula bar for each one. I'm sure there must be a better way. Thanks for the better way :), Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Cells that Get Value from Macro
Hi, and thanks for responding.
The problem is that the named cell is referenced from inside the macro--not from inside the spreadsheet. For instance, consider this macro: Function AddIt(ByVal NumToAdd) As Integer AddIt = Range("NumFromSheet").Value + NumToAdd End Function Also consider the following: NumFromSheet is the name of cell A1. A1 has the value 10. Another cell, say B1, has its value set to: =AddIt(6) B1 displays 16. When I change NumFromSheet's value to 15, B1 still displays 16 until I click into its formula to force an update at which time it displays 22. I want to find out how to get B1 to update as a result of entering a new value into NumFromSheet. Thanks in advance! Ken "Tom Ogilvy" wrote in message ... Have you macro put in a formula rather than a value. Assume E9 is the cell they reference. In the cell where the value is place, the macro should put the formula =E9 rather than the value displayed in E9. As an example. -- Regards, Tom Ogilvy "Ken" wrote in message om... Hi. I've written a VB macro that references a cell by name via the Range function. I call the macro from various cells in my spreadsheet. The value the macro returns becomes the value of the cell. I'd like the cells that use the macro to automatically update when the referenced cell value is changed. This is not happening. What do I need to do to get that to work? If it can't be done, is there a manual way to cause all cells on the worksheet to get refreshed all at once? As it stands, the only way I can update the cells is to go through them manually, one at a time, and click inside the formula bar for each one. I'm sure there must be a better way. Thanks for the better way :), Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Cells that Get Value from Macro
Excel minimizes the amout of work it must perform on a recalc by not
recalculating cells that don't need it. It determines this by looking at the arguments of the function of the cell. You have cleverly disguised the dependency on A1 by concealing that dependency in the code, so Excel doesn't see any dependency and never recalculates your formula. If you want to continue using this construct, then the only other alternative is to make the function volatile or leave it the way it is and combine it with a volatile function. Function AddIt(ByVal NumToAdd) As Integer Application.Volatile AddIt = Range("NumFromSheet").Value + NumToAdd End Function so whenever there is a calculation, cells with this formula will be calculated. An alternative is to do =Addit(6)+rand()*0 and leave your function the way it is. Rand() is volatile, so the whole string will be reevaluated on each calculation. Perhaps this is a simplification, but if this is your actual function, I don't see what you are gaining over using =NumFromSheet+6 Which will update. -- Regards, Tom Ogilvy "Ken" wrote in message m... Hi, and thanks for responding. The problem is that the named cell is referenced from inside the macro--not from inside the spreadsheet. For instance, consider this macro: Function AddIt(ByVal NumToAdd) As Integer AddIt = Range("NumFromSheet").Value + NumToAdd End Function Also consider the following: NumFromSheet is the name of cell A1. A1 has the value 10. Another cell, say B1, has its value set to: =AddIt(6) B1 displays 16. When I change NumFromSheet's value to 15, B1 still displays 16 until I click into its formula to force an update at which time it displays 22. I want to find out how to get B1 to update as a result of entering a new value into NumFromSheet. Thanks in advance! Ken "Tom Ogilvy" wrote in message ... Have you macro put in a formula rather than a value. Assume E9 is the cell they reference. In the cell where the value is place, the macro should put the formula =E9 rather than the value displayed in E9. As an example. -- Regards, Tom Ogilvy "Ken" wrote in message om... Hi. I've written a VB macro that references a cell by name via the Range function. I call the macro from various cells in my spreadsheet. The value the macro returns becomes the value of the cell. I'd like the cells that use the macro to automatically update when the referenced cell value is changed. This is not happening. What do I need to do to get that to work? If it can't be done, is there a manual way to cause all cells on the worksheet to get refreshed all at once? As it stands, the only way I can update the cells is to go through them manually, one at a time, and click inside the formula bar for each one. I'm sure there must be a better way. Thanks for the better way :), Ken |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Cells that Get Value from Macro
"Tom Ogilvy" wrote in message ...
Perhaps this is a simplification, but if this is your actual function, I don't see what you are gaining over using It's not the actual function, just a simple representation of the idea. I thought about your advice and realized all I needed to do was add a parameter called something like Number and use it to pass in NumFromSheet--instead of just referencing NumFromSheet directly in the macro. Excel sees what's up and the update works. Thanks for the guidance! Ken =NumFromSheet+6 Which will update. -- Regards, Tom Ogilvy "Ken" wrote in message m... Hi, and thanks for responding. The problem is that the named cell is referenced from inside the macro--not from inside the spreadsheet. For instance, consider this macro: Function AddIt(ByVal NumToAdd) As Integer AddIt = Range("NumFromSheet").Value + NumToAdd End Function Also consider the following: NumFromSheet is the name of cell A1. A1 has the value 10. Another cell, say B1, has its value set to: =AddIt(6) B1 displays 16. When I change NumFromSheet's value to 15, B1 still displays 16 until I click into its formula to force an update at which time it displays 22. I want to find out how to get B1 to update as a result of entering a new value into NumFromSheet. Thanks in advance! Ken "Tom Ogilvy" wrote in message ... Have you macro put in a formula rather than a value. Assume E9 is the cell they reference. In the cell where the value is place, the macro should put the formula =E9 rather than the value displayed in E9. As an example. -- Regards, Tom Ogilvy "Ken" wrote in message om... Hi. I've written a VB macro that references a cell by name via the Range function. I call the macro from various cells in my spreadsheet. The value the macro returns becomes the value of the cell. I'd like the cells that use the macro to automatically update when the referenced cell value is changed. This is not happening. What do I need to do to get that to work? If it can't be done, is there a manual way to cause all cells on the worksheet to get refreshed all at once? As it stands, the only way I can update the cells is to go through them manually, one at a time, and click inside the formula bar for each one. I'm sure there must be a better way. Thanks for the better way :), Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating links in a macro | Excel Discussion (Misc queries) | |||
Macro updating a new column each day | Excel Worksheet Functions | |||
Regarding auto updating Macro | Excel Discussion (Misc queries) | |||
Have a Macro run after updating cells | Excel Discussion (Misc queries) | |||
Macro for updating links | Excel Programming |