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

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

MDeaves

Intermediate

Posts: 8

Date of registration: Jun 5th 2012

Tuesday, July 17th 2012, 11:08pm

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

function getcell() { 
  
$value1 = activeworkbook()->sheets('Sheet1')->Range('A1')->value; //copy first input to $value1
activesheet()->range('B1')->value=$value1; //paste $value1 into cell B1 to be calculated 
$value2 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value2 
activesheet()->range('D1')->value=$value2; // past $value2 into cell D1
  
  
$value3 = activeworkbook()->sheets('Sheet1')->Range('A2')->value; //copy second input to $value3
activesheet()->range('B1')->value=$value3; //paste $value3 into cell B1 to be calculated 
$value4 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value4 
activesheet()->range('D2')->value=$value4; // past $value4 into cell D2
  
  
$value5 = activeworkbook()->sheets('Sheet1')->Range('A3')->value; //copy third input to $value5
activesheet()->range('B1')->value=$value5; //paste $value5 into cell B1 to be calculated 
$value6 = activeworkbook()->sheets('Sheet1')->Range('C1')->value; // copy the result of calculation to $value6 
activesheet()->range('D3')->value=$value6; // past $value6 into cell D3 
  
}

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!

Michael

Go to the top of the page

laloune

Sage

Posts: 216

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

Wednesday, July 18th 2012, 7:15pm

Hi Michael,

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

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

$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: http://cz2.php.net/manual/en/control-structures.for.php

adapted to the macro engine:

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

you can try now to adapt this to your need

laloune

"To understand recursion, one must understand recursion"

Go to the top of the page

MDeaves

Intermediate

Posts: 8

Date of registration: Jun 5th 2012

Wednesday, July 18th 2012, 9:06pm

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

function Go_Click (){

    for ($i=0;$i<=3;$i++)  
  {
  
    $input='A'.$i;
    $calculation='B1';
    $output='C1';
    $list='D'.$i;
    
    activesheet()->range($calculation)->value=activesheet()->range($input)->value;

    activesheet()->range($list)->value=activesheet()->range($output)->value;
  }
  
}

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.

Go to the top of the page

MDeaves

Intermediate

Posts: 8

Date of registration: Jun 5th 2012

Wednesday, July 18th 2012, 9:17pm

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

function Go_Click (){

    $i=activesheet()->range('F1')->value;
    $input='A'.$i;
    $calculation='B1';
    $output='C1';
    $list='D'.$i;
    
    activesheet()->range($calculation)->value=activesheet()->range($input)->value;

    activesheet()->range($list)->value=activesheet()->range($output)->value;

    activesheet()->range('F1')->value=activesheet()->range('F1')->value+1;
}

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

Go to the top of the page

Si_Ritchie

Trainee

Posts: 2

Date of registration: Jul 28th 2010

Location: Brighton, England

Occupation: BI Consultant

Thursday, July 19th 2012, 1:46am

Have you tried:

	Source code
1	activesheet()->calculate;

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

Source code

function Go_Click (){
  

    for ($i=1;$i<=30;$i++)  
    {
  
    $input='A'.$i;
    $calculation='B1';
    $output='C1';
    $list='D'.$i;
    
    //if there is no input value then break out of the loop
    if(activesheet()->range($input)->value==""){
      break;
    }
    
    activesheet()->range($calculation)->value=activesheet()->range($input)->value;
    
    //calculate current sheet
    activesheet()->calculate;
    
    activesheet()->range($list)->value=activesheet()->range($output)->value;
    
    }
  
}

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