Actualize values and referenced formulas in VBA

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Actualize values and referenced formulas in VBA


    I have to do the following - all in a VBA-Macro:
    I change an element. Then I have to actualize the values from the database and further I have to actualize referenced formulas to this values. They do something depending the value of the database.

    My problem: I saw that wenn I do changes of element-selections with the macro, the database values are #NV. And it keeps this #NV until the macro has finished so I have no chance to actualize the further formulas within the same macro.

    How can I get the actual values from the database within VBA-macro-procedure (without user-interaction between)?

    Thank you for your help.

    A little addition: When I wrote about the referenced formulas and actualized values, it was meaned they are in an Excel Workbook (I like to have the acutalized values in the Excel-Workbook in the runtime of the macro :))

    The post was edited 2 times, last by rapid ().

  • hello rapid,
    if i understood your problem right, you only need the values of the database in the macro, don't you? -> then you might use the palo classmodule posted in thread
    with this module you can work with palo-formulas in your macros.
    or you simply try re-calculating the workbook if you like the updated values in the workbook

    The post was edited 2 times, last by Silba ().

  • hello silba

    thank you for your answer. It's a fact that I need the values in the workbook. In this VBA-macro-procedure I have to do some things with this values on the workbook - e.g. I have to copy and paste only values to another place in the workbook; then I have referenced formulas in the workbook that should be actualized in the same macro-procedure.

    If I make one (or more) re-calculations per this vba-procedure in the workbook it doesn't help because the values are then #NV. They will be actualized when the macro-procedure has finished (and that's too late in this case...).

    What could help is a possibility to get the actualized values in the workbook while the macro is active.

    The post was edited 1 time, last by rapid ().

  • It seems like I found a solution. Until now I used the DATAC-Formula. This generated a #NV-Value when a VBA-macro is running.

    With the DATA-Formula it seems to be ok. There is no #NV-Value when I think right, there is the actualized value.

    Thank you for your hints :)

    The post was edited 1 time, last by rapid ().