Running PHP Macro one line at a time instead of all at once in Palo Web

    Running PHP Macro one line at a time instead of all at once in Palo Web

    Hi,

    I am running a large worksheet in Palo Web. This spreadsheet essentially does the same calculation many times. In order to make it smaller and easier to error check I thought I would only code one calculation and then develop a macro to sequentially change the inputs, one by one, and copy the outputs to separate output cells. For example, if my inputs are listed in cells A1, A2, A3, the calculation takes cell B1 and outputs cell C1, and I need the outputs listed in cells D1, D2 and D3, I would want something like this,

    Source Code

    1. function getcell() {
    2. $value1 = activeworkbook()->sheets('Sheet1')->Range('A1')->value; //copy first input to $value1
    3. activesheet()->range('B1')->value=$value1; //paste $value1 into cell B1 to be calculated
    4. $value2 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value2
    5. activesheet()->range('D1')->value=$value2; // past $value2 into cell D1
    6. $value3 = activeworkbook()->sheets('Sheet1')->Range('A2')->value; //copy second input to $value3
    7. activesheet()->range('B1')->value=$value3; //paste $value3 into cell B1 to be calculated
    8. $value4 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value4
    9. activesheet()->range('D2')->value=$value4; // past $value4 into cell D2
    10. $value5 = activeworkbook()->sheets('Sheet1')->Range('A3')->value; //copy third input to $value5
    11. activesheet()->range('B1')->value=$value5; //paste $value5 into cell B1 to be calculated
    12. $value6 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value6
    13. activesheet()->range('D3')->value=$value6; // past $value6 into cell D3
    14. }​



    The problem is that when I run it the code doesn't step through consecutively. It does all the calculations in one step, so it takes what ever is in C1 when the function is called and places it in all three of D1, D2, D3.

    Is there a way to make it step through the calculation? Maybe with a for loop? What would the syntax be like for a for loop?

    Thanks so much!
    :D


    Michael
    Hi Michael,

    the syntax for a for loop in php is the following:

    PHP Source Code

    1. for ($var=start;$var<=end;$var++)
    2. {
    3. ... statements
    4. }


    $var++ increments the variable $var of 1. If you need to increment with a bigger step, you can use: $var=$var + 2

    more info here: cz2.php.net/manual/en/control-structures.for.php

    adapted to the macro engine:

    PHP Source Code

    1. function looping()
    2. {
    3. for ($i=0;$i<40;$i++)
    4. {
    5. $cell='A'.$i;
    6. activesheet()->range($cell)->value=$i;
    7. }
    8. }


    you can try now to adapt this to your need
    laloune

    Post hoc, non est propter hoc
    Ok so we are getting a bit closer. The code from above was adapted by using a for loop to look something like,

    Source Code

    1. function Go_Click (){
    2. for ($i=0;$i<=3;$i++)
    3. {
    4. $input='A'.$i;
    5. $calculation='B1';
    6. $output='C1';
    7. $list='D'.$i;
    8. activesheet()->range($calculation)->value=activesheet()->range($input)->value;
    9. activesheet()->range($list)->value=activesheet()->range($output)->value;
    10. }
    11. }



    Where the input values are listed in cells A1, A2 and A3. The input to the calculation is in cell B1, the output from the calculation is in cell C1, and the list of outputs I want listed in D1, D2 and D3.

    Unfortunately I get the same result as before. The code executes all in one go, and doesn't give enough time for the cell C1 to take new new input placed in B1 and calculate a new number. All that happens is what ever is in cell C1 when the macro starts gets placed in cells D1, D2 and D3.

    Somehow there has to be a way to tell the macro to take the NEW number that appears in C1 after the new input has been placed in B1 and not just take what ever is there at the beginning.

    Any help is much appreciated. :D
    I have made a little progress again by essentially breaking the for loop down into one step pieces executed by the macro. The I just have to run the macro 3 times to get the result I want.

    Source Code

    1. function Go_Click (){
    2. $i=activesheet()->range('F1')->value;
    3. $input='A'.$i;
    4. $calculation='B1';
    5. $output='C1';
    6. $list='D'.$i;
    7. activesheet()->range($calculation)->value=activesheet()->range($input)->value;
    8. activesheet()->range($list)->value=activesheet()->range($output)->value;
    9. activesheet()->range('F1')->value=activesheet()->range('F1')->value+1;
    10. }



    Is there a way then to run a macro three consecutive times with just one click of a button?
    Have you tried:

    Source Code

    1. activesheet()->calculate;


    This the code i just tested and it seems to work as planned.

    Source Code

    1. function Go_Click (){
    2. for ($i=1;$i<=30;$i++)
    3. {
    4. $input='A'.$i;
    5. $calculation='B1';
    6. $output='C1';
    7. $list='D'.$i;
    8. //if there is no input value then break out of the loop
    9. if(activesheet()->range($input)->value==""){
    10. break;
    11. }
    12. activesheet()->range($calculation)->value=activesheet()->range($input)->value;
    13. //calculate current sheet
    14. activesheet()->calculate;
    15. activesheet()->range($list)->value=activesheet()->range($output)->value;
    16. }
    17. }


    Beware when using the activesheet()->calculate method on a sheet that has dynaranges in. I think the formula get calculated, but it doesnt refresh the dynaranges. I think in the latest version (3.3) they have added a full recalc method.

    I am waiting for a range()->calculate function.

    Enjoy!
    Simon