Macro triggered by cell change

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

  • Hi,
    you can use macros as formulas in cell jsut like default formulas, and they will be executed on refresh (they are registered as so-called "volatile" functions, meaning they are always calculated regardless of whether a depending cell changed or not).

    Example:

    Source Code

    1. function myfunc($input) {
    2. // resolve the cell reference which is passed as string...
    3. $input_cell_value = activesheet()->range($input)->value;
    4. //... then add 1 to its value...
    5. $output = $input_cell_value+1;
    6. //... and return it
    7. return $output;
    8. }


    You can set this in a cell, e.g.
    =MYFUNC("A1")

    If you then put =RAND() function in A1, the cell with macro func will return result of RAND() + 1 on every refresh.

    Note that you can not use all macro functionality when using the function as formula. For example, you can not trigger re-calculations, or return a message box etc.
  • Hi,

    thanks for the quick response Dominik. Due to your hint

    dominik_l wrote:

    Note that you can not use all macro functionality when using the function as formula. For example, you can not trigger re-calculations, or return a message box etc.
    I am not sure what functions I can use. So I want to further specify my need to check if it will work. The idea is to have a combobox with an entire dimension (> 30.000 elements) and additionally a cell where a manual element entry is translated into element selection without reducing the combobox's elementlist.

    My first shot was to have the text-cell be transformed into the filter variable of the text-filter in the combobox's subset. This however reduces the combobox's element list to only the selected element.
    Thereafter, I decided to not use a name as target of the combobox, but a cell. In order to have the manual entry be placed into the combobox's target cell (cannot be the same as elementname is too technical for users, thus has to be transformed) I need SOMETHING. This SOMETHING, I tried to find in a macro.

    So here are my questions:
    1. Is there a leaner way to achieve my goal?
    2. Would a macro-function as Dominik described do the trick of changing another cells value?
    Thanks a lot in advance
  • I think for your purpose it's enough to simply use a variable as target of the combobox.
    If you then put a variable expression in a cell, e.g. for variable "myvar":
    =@myvar
    further input on this cell will not overwrite that expression. Instead, whatever you type there will be set as value of the variable, and it would also change the selected value of the combobox (if that value is part of the combox's source list, i.e. subset).