Jump to content

Worksheet Calcs from Space objects

Recommended Posts

Hi I'm trying to create a worksheet that lists the area and volume of earthworks for a project. I have used the existing/proposed site models to get general cut / fill volumes however I don't want to model the dtm to reflect the earthworks required for carparking, foundations, piles etc. Instead I have created space objects with the correct area & heights of these elements.


The worksheet uses the space objects criteria to bring the heights, areas, volumes into the worksheet. It looks good except the "totals" aren't summing the values for the volumes, instead just summing the number of entries. Any suggestions??




Link to comment

Thanks Pat. I don't think there is one for volume. I found another one for area which worked as a number rather than text but not one for volume. Will double check again tomorrow.


is there any way to edit record format of a PIO like this?

Link to comment

Space.Volume and Space.GrossVolume should return numbers.  Space.11_Volume returns a string.


No there is not a good way to edit the record format. You can go to Tools:PlugIns:PlugIn Manager and view the record format and even change the field names and defaults, but the next time NNA updates the program and edits the space object you will lose all your changes and have to make them again. If you need more stuff, you are better off attaching a second Custom Record format.


This thread has a script that will show you all of the fields in a PIO record and their types. I linked to the latest version, but you probably should scan the thread to see how to use the script.



Link to comment

Thanks Pat. Indeed, in the create report dialogue there is an alternative option : "Gross Volume Number" which provides the record "'Space'.'Gross Volume'. So I now have a nice Earthworks Calcs Spreadsheet:






I tried importing the PIO worksheet script you linked to however had some issues.  Have posted a comment in that.

Link to comment
  • 1 year later...

Hi Boh,


I'm working on a similar application but I don't quite know how to make my worksheet recognize cut and fill values as numbers and not strings (as it seems it cannot use strings for making calculations. I see in your worksheet above that your cut and fill values are adding up in a totals row. I have used the "Create Report" option in Tools to generate a database row that shows only site models and provides some field information about the site model (row 7 in my worksheet below). When I try to do calculations on these site model values, I get unexpected results. 


How are you pulling your cut and fill values in your worksheet? 


Thanks so much ,





Link to comment

Many of the VW objects have multiple fields where values are stored in one as a formatted string and another where it is stored as a number.


Send me a file with an object and your worksheet and I will try to find it for you.


Or you can just use the =Value() function to convert the string to a number you can use in a calculation.


Something like:  =Value('DTM6"."Net C&F Volume").  Or you may have to leave the basic formula to get the value in a cell and add a new column with a formula of =Value(B7)


Do you have the cell formatted to display the units? or is that actually part of the string coming from the object? If it is coming from the object you would have to do something like:


=VALUE(SUBSTRING(B7, ' ', 1)). Where ' ' is a space to indicate the end of the number portion and 1 indicates that you want the first substring.


Ask again if this is not clear enough.

Link to comment

@reGenerate Design


I don't have VW2015 set up to be able to test, but the following worked to get the text value as a workable number in VW2020.


=VALUE(SUBSTRING(('DTM6'.'Net C&F Volume'), ' ', 1))


If you then format the cell at Dimension Volume it will convert to whatever volume unit you have set in Document preferences and show the units while still being stored as a number.


It is possible that SubString (and maybe even Value) were not added to worksheets until after VW2015. In which case you are out of luck until you upgrade.  Try and insert a function into a cell from the menu and see if SubString and Value show in the list.

Link to comment

That most likely means that the functions were not available in 2015. There were a lot of enhancements to worksheets around VW2018 (17?). It looks like you will have to manually type in the numbers or update VW.




One last option.  In the Worksheet Function list is there an entry for RunScript? If so I can probably give you a script that will return the value. If no RunScript you are truly stuck.

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...