Worksheet and Script

[Thomas W]

Hello everyone!

 

I have a problem with a script that searches for elements in a spreadsheet.

The script works when the worksheet is closed or opened but not by executing it in a database line with the RunScript('') function, the result is visible on the attached screenshots.

 

In summary :

I created a door plugin with different accessory options that we find in the worksheet.

The UUID of each door and of each door concerned by each Options is displayed in the worksheet, as on the captures.

Then the objective is to group the results by UUID to know which options concern which doors.

The set will be imported into a software to manage the orders, that's why we have to group them like this.

 

Does anyone know why the script works by running it when the worksheet is open or closed but not with the RunScript() function?

I get crashes whenever I try to run it in a database cell or row.

I would like to run the script in the worksheet without the user needing to run it manually. Do you have an idea to do this?

 

Copying data from sublines is quite slow but if the result is right and if it's stable that's fine with me!

Testing on larger files is quite slow but gets the job done...

 

You will find attached the .vwx file and the plug-in used.

 

Thank you in advance and have a nice day everyone,

 

Thomas

Test Plugin Menuiseries Extérieures 05 Simplifié V5+Script UUID et autres-V5 - Forum.vwx TW 14_PP 60 Simplifié V2.vso

[Pat Stanford]

This is more complicated than I really have time to dive into.

 

But my first thought is that you don't need or want to put the GetSubrow script in the subrow. Since it runs OK from outside the worksheet, why not just put it in a worksheet cell so that it will only run once when the worksheet is recalculated.

 

But even there is a problem. Since you have Recalculate in the script and the script runs every time the worksheet is recalcuated, you are probably creating a recursion loop that will cause problems.

 

I don't think that there is an option other than to turn on automatic recalculation and to teach your users to recalculate the worksheet.  Worksheets will recalculate when you Publish, so your data should be ok on export, but might not be 100% up to date unless the user manually recalculates.

 

HTH

[Thomas W]

Hi Pat and thanks for your answer.

 

I had tried to run the script from a cell worksheet but Vectorworks crashed every time.

I'll search if I find a recursion loop that will cause problems, thanks for the lead!

I had tried running the script in debug mode from a cell to locate the problem and noticed that the script was not running in the same order as outside the worksheet.

 

I'll look in that direction and let you know if I manage to fix this crashing issue.

 

Thanks, and have a good day!

 

Thomas

[Thomas W]

Hello @Pat Stanford,

 

Thanks for putting me on the track with: "you are probably creating a recursion loop"!

 

I found the problem or one of the problems that caused Vectorworks to crash when the script is executed in a cell with RunScript.

From what I could see when SetWSCellFormulaN inserts a formula the sheet is recalculated and the script returns to H1, visible in the screenshot below, loops from line 31 to 17, then crashes.

 

Searching I found the post below on "RecalculateWS" function and an answer from @_c_ which had me test GetWSAutoRecalcState and SetWSAutoRecalcState, it solved the problem in my case!

 

https://forum.vectorworks.net/index.php?/topic/31084-recalculate-individual-worksheet/#comment-153151

 

Below is part of the script that works by running it with RunScript and stopping automatic recalculation of the worksheet while writing to cells and re-enabling it after writing cells.

 

The gain in writing speed is enormous!

It works pretty well and fast for copying about 30 cells, I'll tell you if it works just as well for 100 or more cells.

If you have any comments or advice on this subject, I would gladly read them!

 

PROCEDURE GetWSSubrowcellStrg;
{$DEBUG}

VAR
	H1 :HANDLE;
	NumSubrows1, NumSubrows2, DatabaseRow, DatabaseRow2 :INTEGER;
	Row, Column, Subrow1, Subrow2, SubrowEnd :INTEGER;
	rowStart, rowEnd, columnStart, columnEnd :INTEGER;
	I1, CheckedString :INTEGER;
    cellStrg, cellStrgA, cellStrgB, ref, ref1, ref2 :DYNARRAY OF CHAR; 
    OK, State :BOOLEAN; 
    ObjectName, WorksheetName :STRING;
	
BEGIN
	 H1 := GetObject('Tableau-TW 14 PP 60_V2');	{ WorksheetName }
	 
	{ H1 := GetTopVisibleWS; }
	
	DatabaseRow := 38;
	GetWSSubrowCount(H1, DatabaseRow, NumSubrows1); 	{ Returns a count of displayed subrows for a specified database row }
	AlrtDialog( Concat('DatabaseRow = ', DatabaseRow, CR, 'NumSubrows1 = ', NumSubrows1));
	
	State := GetWSAutoRecalcState(H1);
	AlrtDialog(Concat('State = ', State));
	SetWSAutoRecalcState(H1, FALSE);	{ Sets the AutoRecalc flag for the specified worksheet } { Without this function when the script is executed with RunScript a recursion loop is created then Vectorworks crashes }
	FOR Subrow1 := 1 TO NumSubrows1 DO { nombre de sous-lignes de la base de données concernée }
		BEGIN
			GetWSSubrowCellStrN(H1, DatabaseRow, 5, Subrow1, cellStrgA);	{ Returns the displayed string in a database subrow cell. }
			GetWSSubrowCellStrN(H1, DatabaseRow, 1, Subrow1, ref);			{ Returns the displayed string in a database subrow cell. }
			{AlrtDialog(Concat('Subrow1 = ', Subrow1, CR, 'cellStrgA = ', cellStrgA));}
			SetWSCellFormulaN(H1, (60+Subrow1), 5, (60+Subrow1), 5, cellStrgA);		{ Inserts a formula into a cell of the referenced worksheet. }
			SetWSCellFormulaN(H1, (60+Subrow1), 1, (60+Subrow1), 1, ref);
		END;
	
	SetWSAutoRecalcState(H1, State);
	
	{ RecalculateWS(H1); }		{ do not activate here if executed with RunScript }
	RESETOBJECT(H1);
	RESETOBJECT(GETWSIMAGE(H1));
		
END;
Run(GetWSSubrowcellStrg);		

 

Thanks and have a great day!

 

[Pat Stanford]

Are you putting the script into a worksheet cell or a database cell?

 

Based on my quick read of the code, since you are not using the WSScript_GetItem or WSScript_Set functions, you don't need to put it in the database. Put it in a worksheet cell and then it will only run once. If you put it in the database header row it will run once for every item in the database.

 

Glad I was able to point you in the right direction.

[Thomas W]

Hello Pat,

 

I put the script in a worksheet cell, it works pretty well and fast so far for copying values from 160 cells.

It only runs once but if I leave the RecalculateWS(H1) function at the end of the script it loops and crashes.

With the SetWSAutoRecalcState function which sets the AutoRecalc flag for the specified worksheet it works.

 

Thanks again for the good direction!

 

Have a great day,