Jump to content

Custom Worksheet Functions

Recommended Posts

Starting in VW2016, you can create custom functions for use in worksheets using the RunScript function.

There are many ways to store a script to run using the RunScript function, but I have chosen for this example to store it in the Users Folder in Libraries/Defaults/Report~Schedules as a file named SumFieldByCriteria.txt. The file attached can be downloaded and stored there.

In a worksheet you call the function as =RunScript(120,'SumFieldByCriteria.txt','RecordName','FieldName','Criteria')

The 120 specifies the location of the script. The 'SumFieldByCriteria.txt' is the name of the script file stored in the above specified location. The RecordName and FieldName should be replaced by the Record and Field you are interested in summing. There is no error checking to ensure that you have picked a numeric field, and you will get an error if it is not.

The Criteria can be inserted using the Insert Criteria command, but will need to be slighlty modified due to the string based transfer into the script.

The Criteria inserted will need to be surrounded by single quotes. Any single quotes inside the criteria will need to be changed to two consecutive double quotes.

For example, a criteria inserted as ((L='Layer-1')) would need to be changed to '((L=''Layer-1''))'

If you have trouble, start with a very simple criteria and gradually add more terms.

An example to sum the Gross Area of all the Space Object in a Layer named 1-Floor in a Spreadsheet cell is:

=RUNSCRIPT(120, 'SumFieldByCriteria.txt', 'Space', 'Gross Area', '(((L=''1-Floor'')& (R IN [''Space''])))')

By modifying the Record, Field and Criteria, you can Sum numeric fields for any object with a record. Either a custom record attached to the object or a PlugIn Object parameter record.

Link to comment

Can you explain how the spreadsheet formula you suggest using

(=RUNSCRIPT(120, 'SumFieldByCriteria.txt', 'Space', 'Gross Area', '(((L=''1-Floor'')& (R IN [''Space''])))')

would differ in result from

=AREA((L="1-Floor") & (R IN ["Space"]))


Also, wouldn't you have to manually enter the area into the record field 'Gross Area' for each object desired?

Link to comment
  • 4 months later...

Sorry for the delayed reply. I am not getting notifications for a lot of posts.

Something like you wrote is certainly possible, but I am not positive if it will work like that.

You may actually need to create a couple of extra columns. Put the .Area in one and the Count in the other and then do the division based on the cell references.

The other possibility is that you can probably use a second SUM tile to actually "UNSUM" the value in the space.area column and get what you want.

  • Like 1
Link to comment

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.

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.

  • Create New...