Jump to content
Pat Stanford

A "Super Short" course in Worksheet Scripts

Recommended Posts

 

In a thread yesterday a question was asked about getting the total area of Space objects contained within a symbol. I could not think of a way to get this information using the standard worksheet functions, but thought it could be done using a Worksheet Script. This seemed like a reasonable project to use as an introduction to Worksheet scripts, SO AWAY WE GO!

 

First what is a worksheet script? A worksheet script is a way to use Vectorscript (or Pythonscript) to create functions that can extend the abilities of the standard worksheet functions. Since it is a full language, you can do ALMOST anything in a worksheet script if you want to try hard enough. Luckily, in many cases you don't have to try too hard and you can use just a few lines of code to generate results that you can't get any other way. While the sample code below looks long, the actual script is only 27 lines. Structured differently it would probably only be about 15 lines. Way more comments in this one than code.

 

Since this is a program, there are lots of different ways I could have accomplished the same result. I chose to write it the way I did because I think this gives the best basis for building other scripts.

 

While Worksheet Scripts can be used in spreadsheet cells, for the purpose of this lesson, we are only going to talk about them in Database rows. When a Worksheet Script is placed in a database header row cell using the formula =RUNSCRIPT('YourScriptNameGoesHere') the script is run once for each object in the database. The Database Criteria determine what objects the script is run for.

 

While you would normally want to do something with the object in the database subrow, you don't have to. A Script of just two lines SysBeep;  Wait(1);  will beep once for each database subrow with a 1 second delay between beeps.

 

More likely you will want to access the object referenced in the subrow. You do this via the WSScript_GetObject command. This returns a Handle (a pointer) to the object. This is kind of like an old fashioned card catalog in a library. The card is not the book, but the card tell you where the book is. Similarly, the handle is not the object, but it gives you a way to reference the object to get or set information about the object.

 

Once you have the handle you get to do the real scripting part. The script will then come back with some data (usually) that you will want to display in the worksheet cell. The sample script below returns a number (specifically a REAL i.e. floating point or decimal number) but you could also return an Integer (whole number) or a string (and a couple of other formats as well.)

 

What you do in the middle of the script is up to you. If you go to the Customization forums, there is a banner at the top of most of the posts that will take you to the scripting documentation.

 

Often, a lot of a script is just using the information about the object in the subrow (in the sample script a symbol) to get information about other objects (the Space objects inside the symbol) and then get information about those objects (the Area of each Space) and do something with that information (Add up all the areas).

 

If you take it step by step, start simply, and ask questions, you will soon be able to extend a worksheet for your special needs.

 

For other examples of what Worksheet Scripts can do, do a forum search for "WSScript_GetObject" which will be in almost every worksheet script and very few others.

 

Copy everything inside the code block below and save it into a new blank Script object [Resource Manger, New Resource, Script. Name the Palette and then the Script. Paste the code into the script and make sure the Vectorscript is selected in the pulldown at the top of the editor window.]

 

Make some symbols with one or more (or even no) space objects. Create a worksheet with a database row with a criteria that will ensure that only Symbols show up in the sub-rows. In a test file something a simple as Type IS Symbol will work. For real use you probably want to specify Layers or Classes as well.

 

In one of the Database Header Row cells enter a formula of =RUNSCRIPT('The name you used for the script goes here'). 

 

Recalculate the worksheet and you should get a dialog box asking for permission to run the script. If you say YES, you will have to say Yes every time you recalculate. If you say Yes Always, you will not be asked again until you close and reopen the file.

 

There is a lot more to learn about Worksheets and Worksheet Scripts, but I think this is about enough for tonight. Try it out and see what you think. Ask questions if you need help. I don't bite. Usually ;-)

 

To review the script and comments, open it up in a wide text editor. The code is pretty well commented.

 

And now for the moment you have all been waiting for, a Worksheet Script to sum the values of the Area field of the Space object Parameter Record of each Space Object inside a symbol.

 

Procedure AreaOfSpacesInSymbols;
{September 15, 2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}
{Here be Monsters. Do not operate script under influence of alcohol.}

Var	H1: Handle;			{Handle to Identify the Object referenced in the database sub-row.}
	TotalArea: Real;    {A global variable to store the sum of the areas of the spaces.}


	Procedure Execute(Hd1:Handle);  {Execute is the name of the handling procedure. Hd1 is the object passed}
	                                {to the Execute Procedure. In this case, the object referenced in the}
									{database subrow.}
		Var Hd2, Hd3:Handle;		{Hd2 & Hd3 are handles to intermediate objects needed to get the final}
									{object we need.}

		
		Begin
			TotalArea :=0;			{Set Total Area to zero so it does not carry over from a previous execution.}
			Hd2:=FInSymDef(GetObject(GetSymName(Hd1)));		{Start at the inside of the parens and work out.}
															{GetSymName returns the Symbol Name of the object in the}
															{database subrow. Database criteria should ensure that}
															{only symbols are passed to this script.}
															{GetObject returns a handle to the object whose name is}
															{passed to it. In this case the name of the symbol in the}
															{database subrow.}
															{FInSymDef returns a handle to the first object in the}
															{symbol definition. From there we will step through each}
															{object in the symbol definition.}
			While Hd2<>Nil do		{Since Hd2 has a handle as long as that handle points to an object it will not}
									{be Nil (or empty). When we run out of object Hd2 will return a Nil value and}
									{we will know that we have processed all of the objects in the symbol definition.}
				Begin				{Trust me about where we need begins and ends. That is a bigger subject that I}
									{can take on with this demo tonight.}
					If GetTypeN(Hd2)=86 then 	{GetTypeN takes a handle and returns the type of object. A type of 86 is}
												{a Plug-In Object (PIO). Since Spaces are PIOs, if the type is not 86}
												{we know it can't be a space.}
						Begin
							Hd3:=GetParametricRecord(Hd2);						{But since there are other types of PIOs}
																				{we need to do more checking. The}
																				{Parametric Record of a PIO has the name}
																				{of the PIO. So we get a handle (Hd3) to}
																				{the Parametric Record of the PIO (Hd2)}
							If ((Hd3 <> Nil) & (GetName(Hd3)='Space')) then	{Hd3 <> (not equal) to Nil means that the}
																			{object did have a parametric record. We also}
																			{test that the object is a Space by making sure}
																			{the name of the parmetric record [GetName(Hd3)}
																			{is equal to a string of 'Space'. The single}
																			{quotes around Space make it a "String Literal"}
																			{rather than a variable.}
														
								Begin				{again trust me on the Begin/End pairs. Just know they must be pairs}
									TotalArea := TotalArea + Str2Num(GetRField(Hd2,'Space','Area'));	{See comment below}
														{GetRField returns the value of the field 'Area' of }
														{the Record 'Space' of the object pointed to by Hd2}
														{Since all data in records is stored as strings we have to}
														{convert the string to a number so we can add us the areas.}
														{Str2Num takes the string value and return a decimal [Real] number.}
														{We add the value from this space to the value that was previously}
														{stored in the variable TotalArea and store this new value back}
														{into the variable TotalArea. Since we set TotalArea to zero}
														{at the beginning of the script. end up with the area for the}
														{spaces just in the one symbol pointed to in the subrow.}
								End;
							Hd2:=NextObj(Hd2);	{Once we have finished with the first obect in the symbol we}
												{store the handle of the NextObject after Hd2 in the symbol }
												{into the varibale Hd2 and then loop back to the top of the}
												{While loop. At the top if Hd2 has become Nil, meaning that we}
												{have looked at all the objects in the symbol definition, we stop}
												{and return to the main program.}
						End;
				End;
		End;
		
Begin
	H1:=WSScript_GetObject;				{WSScript_GetObject returns a handle to the object in the database}
										{subrow. If a cell contains multiple objects because it has been}
										{SUMmarized in the worksheet, the script will be run once for each}
										{object as if the row was not SUMarized but will return the total}
										{value for each run if the returned value is a number.}
	Execute(H1);						{Pass the value of the handle of the object in the subrow, in this case}
										{a symbol to the Procedure Execute.}
	WSScript_SetResReal(TotalArea);		{WSScript_SetResReal [Set Result Real] causes the value to be }
										{displayed in the WS cell to be a number, in this case the value}
										{of the variable TotalArea that we calculated above.}
End;		{and yet another End to match a Begin. Also just trust me on the Procedure line at the top}
			{and the Run line at the bottom. They just have to be there.}

Run(AreaOfSpacesInSymbols);	
									

 

I feel a little like Ferris Buehler here. "Your still here? Go home. It's over."

 

But it's not over. There are ways to store a script in a library file so you can use it in any VW file rather than having to have it stored in each file. You can script almost anything. We can discuss the differences between the Lesser General Public License (the one I used for the sample code) versus the GPL or MIT licenses or even Copyleft. Basically the LGPL says you can use this for anything you want, but if you modify this code and use it you are supposed to share the modified code under the same terms. I am not prosecuting anyone who doesn't comply.

 

And I know the comments are kind of strange. I tried to block things together so that they make some sense, but I wanted to keep the lines as long as possible, so they start at different indents.

 

That could be another thing we talk about (actually I don't think it can be discussed civilly, only argued about) which would be the proper way to indent code, to capitalize variable names, and how to use Begin/End statements. Just to be clear, I have used my preferred way in the sample code. You should use that way too. It is the right way. Unless it it not the right way for your brain. In which case I wish you luck in finding a way that does work for your brain. Or not.

 

Oh, and don't forget the three most important parts. 1. The reference documents and the forum are your friends and can get you out of almost any hole you dig for yourself. 2. You are more stubborn than the computer and you can just keep trying things until you make it work. 3. If it just won't work, walk away and come back and try later.

 

Good luck, have fun, ask questions, beat your head on the desk, curse, scream, rejoice.

 

And never forget this is programming. Here be Dragons.

 

HTH.

 

Area of Spaces in Symbols.vwx

  • Like 3
  • Confused 1

Share this post


Link to post

Thanks a lot @Pat Stanford I can almost understand what you are writing 

My first test did not go well, VW got unresponsive, but I will try more later today and report back. 

Thanks again!

  • Like 1

Share this post


Link to post

I gave this a try as well, because we have a project with different apartement units in symbols. Would be really handy to calculate the area like this. But like you I can't make it work. Vectorworks becomes unresponsive. Just duplicating the symbols inside the example file works, but adding new symbols causes issues.

  • Like 1

Share this post


Link to post

I think the issue is with symbols that contain more than just the space. I created a new file with only spaces in symbols and it worked.🤔

Share this post


Link to post

Probably a bug in my code. I  did not test very well. I will test tonight on a symbol with objects other than Spaces. My guess is that I never move to the Next Object if the object is not a Space.

 

Watch this space for more information.

  • Like 2

Share this post


Link to post

OK. So you see, I found the problem. I mean. Yeah. I mean. Right. Ok. I meant to do that. I meant to do that as part of the course. Of course I did. I wanted to make a mistake and see if you are learning enough to figure it out. Right. You believe me right? Really, you really believe me, right??

 

Sorry about that just a little bit of insecurity bubbling up.

 

I messed up in the script.

 

I guess I am going to have to do the course on Begins and Ends after all. But not tonight. I got the NextObject statement one line too high. That put it inside the loop that checks if the object is a Space. So if the object in the loop was not a Space, the script Never went onto the next object and just kept checking the first object over and over and over and over. This is what is called in programming an Infinite Loop. And the only way to break out was what you did and quit VW.

 

Here is the revised version of the program. I have tested it with a symbol with both spaces, other PIOS and non-PIO objects. You can probably still break it as it still doesn't do any error checking, but it should not lock up VW now.

 

Procedure AreaOfSpacesInSymbols;
{September 15, 2020}
{©2020 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}
{Here be Monsters. Do not operate script under influence of alcohol.}

Var	H1: Handle;			{Handle to Identify the Object referenced in the database sub-row.}
	TotalArea: Real;    {A global variable to store the sum of the areas of the spaces.}


	Procedure Execute(Hd1:Handle);  {Execute is the name of the handling procedure. Hd1 is the object passed}
	                                {to the Execute Procedure. In this case, the object referenced in the}
									{database subrow.}
		Var Hd2, Hd3:Handle;		{Hd2 & Hd3 are handles to intermediate objects needed to get the final}
									{object we need.}

		
		Begin
			TotalArea :=0;			{Set Total Area to zero so it does not carry over from a previous execution.}
			Hd2:=FInSymDef(GetObject(GetSymName(Hd1)));		{Start at the inside of the parens and work out.}
															{GetSymName returns the Symbol Name of the object in the}
															{database subrow. Database criteria should ensure that}
															{only symbols are passed to this script.}
															{GetObject returns a handle to the object whose name is}
															{passed to it. In this case the name of the symbol in the}
															{database subrow.}
															{FInSymDef returns a handle to the first object in the}
															{symbol definition. From there we will step through each}
															{object in the symbol definition.}
			While Hd2<>Nil do		{Since Hd2 has a handle as long as that handle points to an object it will not}
									{be Nil (or empty). When we run out of object Hd2 will return a Nil value and}
									{we will know that we have processed all of the objects in the symbol definition.}
				Begin				{Trust me about where we need begins and ends. That is a bigger subject that I}
									{can take on with this demo tonight.}
					If GetTypeN(Hd2)=86 then 	{GetTypeN takes a handle and returns the type of object. A type of 86 is}
												{a Plug-In Object (PIO). Since Spaces are PIOs, if the type is not 86}
												{we know it can't be a space.}
						Begin
							Hd3:=GetParametricRecord(Hd2);						{But since there are other types of PIOs}
																				{we need to do more checking. The}
																				{Parametric Record of a PIO has the name}
																				{of the PIO. So we get a handle (Hd3) to}
																				{the Parametric Record of the PIO (Hd2)}
							If ((Hd3 <> Nil) & (GetName(Hd3)='Space')) then	{Hd3 <> (not equal) to Nil means that the}
																			{object did have a parametric record. We also}
																			{test that the object is a Space by making sure}
																			{the name of the parmetric record [GetName(Hd3)}
																			{is equal to a string of 'Space'. The single}
																			{quotes around Space make it a "String Literal"}
																			{rather than a variable.}
														
								Begin				{again trust me on the Begin/End pairs. Just know they must be pairs}
									TotalArea := TotalArea + Str2Num(GetRField(Hd2,'Space','Area'));	{See comment below}
														{GetRField returns the value of the field 'Area' of }
														{the Record 'Space' of the object pointed to by Hd2}
														{Since all data in records is stored as strings we have to}
														{convert the string to a number so we can add us the areas.}
														{Str2Num takes the string value and return a decimal [Real] number.}
														{We add the value from this space to the value that was previously}
														{stored in the variable TotalArea and store this new value back}
														{into the variable TotalArea. Since we set TotalArea to zero}
														{at the beginning of the script. end up with the area for the}
														{spaces just in the one symbol pointed to in the subrow.}
								End;
						End;

					Hd2:=NextObj(Hd2);			{This is the bug fix. This line moved ahead of the End}
												{statement that is now below it.  Sorry about that.}
												{Once we have finished with the first obect in the symbol we}
												{store the handle of the NextObject after Hd2 in the symbol }
												{into the varibale Hd2 and then loop back to the top of the}
												{While loop. At the top if Hd2 has become Nil, meaning that we}
												{have looked at all the objects in the symbol definition, we stop}
												{and return to the main program.}

				End;
		End;
		
Begin
	H1:=WSScript_GetObject;				{WSScript_GetObject returns a handle to the object in the database}
										{subrow. If a cell contains multiple objects because it has been}
										{SUMmarized in the worksheet, the script will be run once for each}
										{object as if the row was not SUMarized but will return the total}
										{value for each run if the returned value is a number.}
	Execute(H1);						{Pass the value of the handle of the object in the subrow, in this case}
										{a symbol to the Procedure Execute.}
	WSScript_SetResReal(TotalArea);		{WSScript_SetResReal [Set Result Real] causes the value to be }
										{displayed in the WS cell to be a number, in this case the value}
										{of the variable TotalArea that we calculated above.}
End;		{and yet another End to match a Begin. Also just trust me on the Procedure line at the top}
			{and the Run line at the bottom. They just have to be there.}

Run(AreaOfSpacesInSymbols);	
									

 

  • Like 2

Share this post


Link to post

Works like a charm for me! You are the man @PatStanford! Thanks a ton! And thanks for keeping it entertaining as well 😄 Sometimes scripting looks like magic, but you make it very easy to understand.

Share this post


Link to post
6 hours ago, Pat Stanford said:

OK. So you see, I found the problem. I mean. Yeah. I mean. Right. Ok. I meant to do that. I meant to do that as part of the course. Of course I did. I wanted to make a mistake and see if you are learning enough to figure it out. Right. You believe me right? Really, you really believe me, right??

 

Haha I was wondering if it was the case. However I tried to analyze the code but could not see what was wrong or how to improve it. Hopefully this tread can help more people looking for similar solutions. Thanks a lot @Pat Stanford 

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×
×
  • Create New...