Custom Worksheet Functions

[Pat Stanford]

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.

[ericjhberg]

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?

[Pat Stanford]

the AREA function will return the Net Area of a space object.

What this was developed for was to get the Gross Area field. Until I developed this function the only option was to use a database and let the header row sum the Gross Area fields.

[Brian H]

Hi Pat,

I hope that all is well.

I'm having a mental block. Is it possible to divide a sum of areas by the count to get an average of the areas?

='Space'.'Area'/COUNT

Thanks,

Brian Hores

[Pat Stanford]

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.