# Pat Stanford

Moderator

5,090

## Posts posted by Pat Stanford

1. ### Get fill color of a class on a worksheet?

OK try the version in this file. I still think something is messing with your copy/paste from the forum into the script editor.

This version is just copied and pasted from above into a new VW2019 file.  Open the Resource Browser to see the Script Palette and run the script. If it does not provide the warning, them import the script into your file and see if it works properly there.

Let me know.

Classes to Worksheet.vwx

2. ### Worksheet Summary Problem w/ Tags and Geometry Ref

It appears that the actual entered dimensions of the windows are not identical, but that they round to the same display value. That would also be why you are not able to get them to Summarize as they are not the same at a computer level even though the are on a human level.

Assuming you Height and Width columns are actually displaying the information from the Window Record (=window.height) and not the worksheet functions (=height), you should be able to enter the height or width in inches into a cell so you can copy it to the clipboard. You can then paste that into a multiple selection of other cells.

If you were in Inches instead of Feet-Inches it would be easier and just be a copy/paste.

Or temporarily change the cell formatting to Decimal to make the copy/paste easier and then format back.

3. ### Stage Deck Fill

After you do the Subtract Solids take a look at the object type in the OIP. It probably says it is a Solid Subtraction.

Solids are 3D only objects. 3D only objects do not offer the ability to have different 2D (Top/Plan) and 3D displays and show as wireframe in Top/Plan.

You have several options to work around this. Which one is right for you will depend on your desired final outcome and how your brain works.

1.  Make a separate 2D object with the fill you want and combine it with the 3D as either a Group or a Symbol. If you may a Hybrid Symbol you will have full control over the 2D and 3D display.

2. Convert the Solid into an AutoHybrid. This will give you the separate 2D and 3D appearances.

3. Don't do the solid subtraction on the Deck. Rather check the Don't Show Deck option and use a slab object (which is already a hybrid object) to manually draw the deck.

And there are probably several other good options also.

Ask again if you need more help.

4. ### Site Model Contour editing

Have not used any of them, but saw a demo of imagaroZ a long time ago at MacWorld and it looked very impressive.

5. ### Dimensioning to grouped items

Do your Class Options include one of the Snap options (Grey/Snap, Show/Snap, Show/Snap/Modify)?

What snap options do you have on in the Snaps Pallet?

I just tried a simple group consisting of a Line, Rectangle, and circle. I then made a Viewport on a sheet layer from it.

When I edit the annotations, and have Snap to Object turned on in the Snaps Pallet, I can snap to all of the points inside the group. With Snap to Intersection turned on, I even get the intersection of objects in the group and objects in the Annotations.

What are you doing differently?

6. ### Get fill color of a class on a worksheet?

Based on another email, it appears that the code is not being properly copied in some browsers. I have reformatted the code above to manually wrap the longest line.

Open your browser window to a very wide width and copy ALL of the text in the code block above.  Open a VW file. In the Resource Browser select the New Resource button at the bottom left. The Type will be Script.  It will ask for a name for the new Script Pallet and then for the new Script. Once you enter the two named the Script Editor window will open. Make sure the language is set to VectorScript and then click in the text area and Paste the script from above. Clic the Gear icon at the top right of the window to compile the code. You should get a dialog box saying "Script Complied Successfully". If not, you did not properly copy the code. Be very careful with pasting the code into a word processor such as Microsoft Word first. It often adds invisible characters that will prevent the code from compiling.

To run the script you double click on it's name in the script pallet.  You could make it into a menu command but that involved learning about the PlugIn Editor and is more complicated.

See the following post for the basics of how and why to use scripts.

7. ### Get fill color of a class on a worksheet?

What version of VW are you using? This script has been tested in VW2019.

Did you copy everything in the script block (from the original Procedure to after the final semicolon after the Run(Classes_to_Worksheet);)?

I just copied from above and pasted into a new script in a new file created from the Architect Imperial template and it runs fine for me.

Let's see if we can figure this out.

8. ### importing title blocks from 2016

Short answer, Yes, the Title Block tool and object changed significantly in VW2018/VW2019. You will need to learn about the new system to use it effectively. Better in many ways.

• 1
9. ### linking text with a worksheet value

This will only work if the text is part of a Symbol.

Create a custom record format with a text field.

Create a block of text and make it a symbol. Edit the Symbol and select the text block. Choose Link Text To Record from the Modify menu. Choose your record and field.

Create a worksheet. Select a row (I alway start at Row 3 in case I want to add headers later) and rich click on the row Header.  Choose Database. From the Edit Criteria dialog box choose Record   "YourRecord"  is Present.  You should end up with the same number of subrows as you have YourText symbol instances.

In any column enter a formula of "=YourRecord.YourField"  If you used spaces in either the Record or Field names, you will need to use single quotes around each name.  Hit Enter. You should now see the text from each symbol instance. Click in the subrow (3.1, 3.2, etc) in the cell that has the data and edit the data. The changed data will show up on the drawing in place of the original text.

Ask again if you need more clarity.  Also check the Help for Link Text to Record, Worksheet Databases, and Records and Fields.

• 1
10. ### WORKSHEET questions, + ENERGOS

I don't think you can reference across files directly.

If you don't need to use the modern Viewport based referencing, you could try and use a Layer Link to bring an updatable version of the worksheet (on a layer) into the file and then you might be able to access the cells on a read only basis.

11. ### Attribute Criteria PP= is not working

@JBenghiat is rarely wrong 😉

I think that sometimes both he and I go a little too fast in trying to help and sometimes answer a different question than is actually being asked.  But if you are persistent we usually figure it out and some up with a working solution.

If you don't care about seeing each line select in turn, you can delete the Message and Wait statements and move the RedrawAll statement to just before the last End statement and the script will run much faster.

12. ### Worksheets Layout

Sorry, but I am just getting back to this.

I don't understand your second point:  "columns for individual lengths and totals for a given class"   Are you asking for this to go horizontally instead of vertically?

I understand a column for the length of each line. I don't understand the second column for the total. Unless you want a running total.

The class and layer part are easy to do using the criteria for the database. Or you can enter a column with a formula of =C and then sort based on that column to pull all of the objects in each class together.

If you still need help with this let me know and I will see what I can do.

13. ### Get fill color of a class on a worksheet?

I thought I had sen the above reply a month ago.  😞

Here is a version of the script that seems to work in VW2019.

Other changes are that the toggle cells to select the classes to show objects from are now formatted as Text so any entry will display the details. Also, the Database Headers are now turned off.

```Procedure Classes_to_Worksheet;
{Make a worksheet listing all of the classes in a VW file}
{Lists both used and unused classes}
{The worksheet is named "Classes:"with and appended date}

{April 12, 2019}
{Modified to work around obsolete VS functions for formatting Worksheets}
{Yellow check mark cells to select classes to detail are now formatted as text so data will execute.}
{Datbase Headers are now turned off this can be changed in the SetObjectVariable Boolean (82) line.}

{January 26, 2017}
{Added code to display the Class Fill color in the ClassName column for the Class}

{April 10, 2015}
{Updated for formatting and to add database integration}
{Set any of the yellow cells in column 1 to non-blank and the related}
{database row at the bottom of the worksheet will show subrows for each item in that class}
{Set the yellow cell back to blank (select, delete, enter/return, and the database subrows}
{will disapper.  The database rows show the Class, Layer and Object type for the related object}

{October 13, 2010}
{Updated to sort classes in alphabetical order prior to storing in worksheet}
{As of VW2011, there is not way to get the display order of the classes in the Nav Palette}
{October 14, 2008}
{© 2019, 2017, 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style:Integer;
R1,G1,B1,BGColor,FGColor: LongInt;
FillPattern: Integer;

Begin
SetCursor(WatchC);

H1:=CreateWS(Concat('Classes:',date(2,1)),(Classnum+2)*2,4);
SetWSCellAlignment(H1,1,1,ClassNum+2,2,5);
SetWSColumnWidth(H1,1,1,20);
SetWSColumnWidth(H1,2,2,200);
SetWSColumnWidth(H1,3,3,200);
SetWSColumnWidth(H1,4,4,200);

Result := WorksheetMergeCells(H1,1,1,1,3);

GetWSCellTextFormat(H1,1,1,Font,Size,Style);

SetWSCellTextFormat(H1,1,1,(ClassNum+2)*2,4,Font,14,Style);
SetWSCellTextFormat(H1,1,1,1,1,Font,18,1);
SetWSCellTextFormat(H1,2,1,2,4,Font,14,1);
SetWSCellTextFormat(H1,Classnum+4,1,ClassNum+4,4,Font,14,1);

SetWSCellBorders(H1,3,1,ClassNum+2,3,True,True,True,True,7);
{SetWSCellOutlineBorder(H1,3,1,ClassNum+2,3,2,25,1);  (*Commented out 4/12/19 Decprecated*)
}SetWSCellBorders(H1,Classnum+5,1,(ClassNum+2)*2,4,True,True,True,True,7);
{SetWSCellOutlineBorder(H1,Classnum+5,1,(ClassNum+2)*2,4,2,25,1);	(*Commented out 4/12/19 Decprecated*)
}
SetWSCellFill(H1,3,1,ClassNum+2,1,1,5,0,1);
SetWSCellFill(H1,ClassNum+5,1,(ClassNum+2)*2,1,1,1,0,1);

For N1:= 1 to Classnum do ClassSort[N1]:=ClassList(N1);

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do
Begin

{*****************************}
{Code in this block is used to set the WS Cell Format to match the Class Fill Color and Pattern}
GetClFillFore(ClassSort[N1],R1,G1,B1);
RGBToColorIndex(R1,G1,B1,FGColor);
GetClFillBack(ClassSort[N1],R1,G1,B1);
RGBToColorIndex(R1,G1,B1,BGColor);
FillPattern:=GetClFPat(ClassSort[N1]);
SetWSCellFill(H1,N1+2,2,N1+2,2,1,BGColor,FGColor,FillPattern);
SetWSCellNumberFormat(H1,N1+2,1,N1+2,1,13,0,'','');  {Added 4/12/2019 Format cells as text}

{This code can be deleted or commented out without effecting other script functionality}
{*****************************}

SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');
SetWSCellFormula(H1,N1+2,2,N1+2,2,Concat(ClassSort[N1]));

Formula:=Concat('=Count(C=',CHR(39),Classsort[N1],CHR(39),')');

SetWSCellFormula(H1,N1+2,3,N1+2,3,Formula);

Formula:=Concat('=Database(C=(IF(A',Num2Str(0,N1+2),'<>',CHR(39),CHR(39),
', B',Num2Str(0,N1+2),',',CHR(39),'PTS!@#\$%^&',CHR(39),')))');

SetWSCellFormula(H1,ClassNum+4,2,ClassNum+4,2,'Class');
SetWSCellFormula(H1,ClassNum+4,3,ClassNum+4,3,'Layer');
SetWSCellFormula(H1,ClassNum+4,4,ClassNum+4,4,'Object Type');

SetWSCellFormula(H1,N1+ClassNum+4,0,N1+ClassNum+4,0,Formula);
SetWSCellFormula(H1,N1+ClassNum+4,2,N1+ClassNum+4,2,'=C');
SetWSCellFormula(H1,N1+ClassNum+4,3,N1+ClassNum+4,3,'=L');
SetWSCellFormula(H1,N1+ClassNum+4,4,N1+ClassNum+4,4,'=T');
SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');  {Added 4/12/2019 Format cells as text}

End;

SetWSCellFormula(H1,1,1,1,1,Concat('Classes in File:',date(2,1)));
SetWSCellFormula(H1,2,2,2,2,'Class Name');
SetWSCellFormula(H1,2,3,2,3,'# of Objects in Class');

RecalculateWS(H1);
SetCursor(ArrowC);

ClrMessage;
ShowWS(H1,True);
End;

Run(Classes_to_Worksheet);```

14. ### Get fill color of a class on a worksheet?

Hi Pascal,

VW2019 broke the script. I will try and find some time to fix it.

There are deprecated (no longer supported) vectorscript procedures in the script and the changes to the Worksheets in 2019 also seem to be a problem.

The script runs great in VW2017 though 😉

15. ### WORKSHEET questions, + ENERGOS

As to your Question 5. Accessing data from a different worksheet in the same file:

Prepend the cell to reference with the worksheet name. The quote marks shown below are only required if there are spaces in the worksheet name.

='Worksheet-1':B10

16. ### WORKSHEET questions, + ENERGOS

I finally had some time to play with this. It does not look like what you are trying to do is going to be easy.

The Energos label contains a very strange array of data containing information about each of the objects that make up the overall Energos model. The length of this array appears to change depending on how many objects are included in the model.

So to get the information from the Energos Label about the objects, you have to manually enter the formula for each object in the array.

The information that appears to be stored for each object is:

```    [0] Object From the Drawing		'EnergyLabel'.'Document.farrBuildingElements.fOriginalObject0'				Text
[0] Is Modified					'EnergyLabel'.'Document.farrBuildingElements.fModified0'					Text
[0] Sort Index					'EnergyLabel'.'Document.farrBuildingElements.fSortIndex0'					Text
[0] Include						'EnergyLabel'.'Document.farrBuildingElements.fInclude0'						Text
[0] Type						'EnergyLabel'.'Document.farrBuildingElements.fType0'						Text
[0] Orientation					'EnergyLabel'.'Document.farrBuildingElements.fOrientation.fOrientation0'	Text
[0] Inclination					'EnergyLabel'.'Document.farrBuildingElements.fOrientation.fInclination0'	Text
[0] Name						'EnergyLabel'.'Document.farrBuildingElements.fName0'						Text
[0] Area						'EnergyLabel'.'Document.farrBuildingElements.fArea0'						Text
[0] UValue						'EnergyLabel'.'Document.farrBuildingElements.fUValue0'						Text```

The number at the end increments for each different object. All of the formulas return TEXT type data.

The  fOriginalObject appears to be a textual representation of the Handle to the original object.

So if you want to access the data (for display purposes, not a 2-way worksheet), you could probably write a script that would read through the Energy Label record and then store the data into a custom record attached to the original objects. You would have to covert the text respresentaion of the Handle into a LongInt to be able to find the original object. You could then do a database of the objects with the custom record to report the data. You would have to rerun the script any time you made a change to the Energos model to update the data.

Once you have the data identified with the individual objects you should be able to report on those objects any way you want in a worksheet. Once column for Orientation and once for =Area. Sum on Orientation and you should get the total area.

It appears doable, but not likely to be fun to program or deal with.

• 1
17. ### Roof pitch assistance

It will depend on how long your roof is.

Alan described the easiest way to do it.

Over a 10 meter horizontal run, the difference in the clearance under the roof will be about 500mm.

18. ### Attribute Criteria PP= is not working

Take a look at this version. It selects all of the lines in the file with the same LineStyle as the first selected object on the layer.

```procedure test;
var Line_Index : longint;
LineStyleName : String;
ListID: longint;
ListItems: LongInt;

Procedure Do_This (H1 : handle);
Begin
SetSelect(H1);
RedrawAll;
Message (GetLSN (h1), '  ', Line_Index);
Wait(1);
End;
begin
ClrMessage;
Line_Index := GetLSN (FSActLayer);
LineStyleName :=GetDashLineTypeName(Line_Index);
DSelectAll;
ForEachObject (Do_This, ((T = Line) & (LT = LineStyleName)));
end;
run (test);```

I had to do some digging to realize the GetDashLineTypeName was the key to getting the LineStyleName so the criteria will work properly.

19. ### NOMAD AR drawing issues

I have seen this before when you have two (or more) surfaces at exactly the same location. When the render engine runs in some places one surface is the one evaluated to show and at the next point over it could be the other surface that shows.

If you can separate the surface by even a little bit (0.1 mm), then the surface conflict can be resolved.

Not sure if this is the issue in your case, but worth a look.

20. ### WORKSHEET - renaming wall style break link

Unfortunately this is WAD (Working as Designed).

The name of the resource is just stored as text. There is no link between the resource and the worksheet other than to look the resource up by name when the worksheet is recalculated.  So if you change the name of the resource, the link is broken and you have to edit the worksheet.

If this is critical to you, there are probably some work arounds involving storing the resource in a record attached to the object and/or possibly using a RunScript call to make a custom worksheet script.

Please submit an Enhancement Request for a way to link a criteria to a resource so the link is not broken when you rename the resource.

21. ### Attribute Criteria PP= is not working

You have to declare a sub procedure (or sub function I can never remember which is used for ForEachObject and which for the FEOInList) and use that as part of the procedure call.

Try:

```procedure test;
var Line_Index : longint;

Procedure Do_This;
Begin
SelectObj ((PP = Line_Index));
Message (GetLSN (FSActLayer), '  ', Line_Index);
End;

begin

ClrMessage;
Line_Index := GetLSN (FSActLayer);
Message (Line_Index); Wait (2);
DSelectAll;
{SelectObj ((PP = Line_Index));
Message (GetLSN (FSActLayer), '  ', Line_Index);
}
ForEachObject (Do_This, ((T = Line) & (PP = Line_Index)));

end;
run (test);```

Too late for testing tonight.

HTH

22. ### Worksheet criteria sort wall styles by name

In Worksheets you use the criteria to determine which objects to display in a Database Row. You put formulas into the Database Header Row (i.e. 3, not 3.1, 3.2, etc.) to determine what data to display.

The correct formula to display the Wall Style is:   =WallStyleName

23. ### offset top end of wall

Wall Peaks created using the Reshape tool and/or Fit Walls to Objects.. can be your friend.

Start with Fit Walls to Objects and it that does not work then manually move the wall peaks to get what you need.

• 1
24. ### clipped roof - restore a portion - how?

If they are still Roof or Roof Face objects after the clipping I believe you can just view in Top/Plan, draw a poly for what you need and then Add Surface.

No guarantees. 😉

25. ### Getting an absolute value of a number in a worksheet

Did that solution come from @michaelk? It looks like one of his math major things.

I probably would have used

=IF(Length>=0, Length, Length*-1)

If this is something that you use a lot, it may be possible to write a Worksheet Script that would be called something like  =Runscript('WS_ABS','A2') to return the absolute value of the data in cell A2.  It is too late tonight to figure out the detail, but I think it is doable.