Worksheet of Classes in a File

[Pat Stanford]

The attached script will create a new worksheet listing the used and unused classes in a file.

Copy the lines from Procedure to Run (inclusive) and paste them into a new blank Vectorscript (created from the Resource Browser). Open the script palette and double click on the script.

The script automatically places the worksheet at the origin of the drawing.

Use as you see fit.

Pat

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}

{October 14, 2008}

{? 2008, Coviana, Inc - Pat Stanford pat@coviana.com}

{Licensed under the GNU Lesser General Public License}

var H1: Handle;

N1: LongInt;

Begin

H1:=CreateWS(Concat('Classes:',date(2,1)),Classnum+2,2);

For N1:= 1 to Classnum do

SetWSCellFormula(H1,N1+1,1,N1+1,1,ClassList(N1));

SetWSCellFormula(H1,1,1,1,1,'Classes in File');

End;

Run(Classes_to_Worksheet);

[Pat Stanford]

Here is an updated version that sorts the classes alphabetically before creating the worksheet. As of VW2011 I don't think there is a way to get the display order of the classes in the Navigation Palette.

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}
{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}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;

Begin
H1:=CreateWS(Concat('Classes:',date(2,1)),Classnum+2,2);

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

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do SetWSCellFormula(H1,N1+1,1,N1+1,1,ClassSort[N1]);	

SetWSCellFormula(H1,1,1,1,1,'Classes in File');


End;
Run(Classes_to_Worksheet);

[SCParker]

Pat,

This is a great help. One of the hard parts of the power of classes is remembering what we have. I don't know how many times I've created a new class for an object just to find later that I should have placed it in an existing class.

Thanks, Scott

[TimG]

Nevermind... i didn't see any other reply's due to my forum settings, I see you updated the script for alphabetical... I'll give that a try. sorry

I got this script to work, Thanks...

However, is there a way I can get it to list in a worksheet alphabetically?

When I run the script as us above, I think it maybe listing them in the worksheet by the order they were created in?

Edited December 2, 2014 by TimG

[Pat Stanford]

OK, I updated this again. This one is kind of nifty.

It goes through and makes a list of all the Classes (used and unused) in a file along with the number of objects in that class. Just like before, but with better formatting.

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

Please note that this is a fairly slow script and worksheet. It takes about 25 seconds on my 2008 MacBook Pro to run on a file with about 50 classes and 1300 objects. It has to make a separate database for each class and every object in the drawing will be included in one of those databases.

Use at your own risk.

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 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}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style: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);
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);

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
	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');

	Message('Number of Classes Processes: ',N1);
	Wait(1);
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);

[michaelk]

Pat

This is really cool. Thanks!

MK

[Pat Stanford]

I thought you would like it Michael. I didn't know that you could use an IF statement in a database definition until I tried it yesterday.

[michaelk]

Every time I take over a drawing from someone else I have a similar worksheet called "Find Crazy Things" that I modify manually to track down objects in a '0' class or find all the windows and doors that are in a DTM-Modifer class.

I'm going to have to take your script apart. I think I get how you did it. Except for PTS!@#$%^&. What's up with that?

mk

[Pat Stanford]

I wanted a "Class Name" that was unlikely to ever be used in an actual file. I figured that if someone used that string as an actual class name there would already be cursing enough that it might be funny to see that displayed for every one of the database lines. But probably not.

[Pat Stanford]

It is probably easier to figure out how this all works from the worksheet than from the script. In the worksheet I just used the script to create a single database line for each class using a formula similar to:

=database(If(A5<>'',C=B5,C='Name never to be used by a real class'))

The trick in the script is to use CHR(39) to generate the single quotes needed as delimiters in the formula.

[michaelk]

That's great.

[Kizza]

Very useful, thx.

[grant_PD]

it seems to be working now. Thanks. I'll let you know how it goes.

[ibenjamin.price]

Nice one Pat! Thanks for posting. This is super helpful. 

[bcd]

Interesting concept Pat - Very nice work.

I could see the concept that underlies this worksheet being incorporated into the Navigation Palette; a Nav+ so to speak.

 

[lgoodkind]

Pat.  Wondered if you have a quick way to list all classes in a worksheet?  Or point me to where I can learn more about doing so? Nevermind - I was able to figure it out.

Edited November 2, 2020 by lgoodkind

[Elite Exhibits]

On 4/10/2015 at 10:18 PM, Pat Stanford said:

OK, I updated this again. This one is kind of nifty.

It goes through and makes a list of all the Classes (used and unused) in a file along with the number of objects in that class. Just like before, but with better formatting.

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

Please note that this is a fairly slow script and worksheet. It takes about 25 seconds on my 2008 MacBook Pro to run on a file with about 50 classes and 1300 objects. It has to make a separate database for each class and every object in the drawing will be included in one of those databases.

Use at your own risk.

 

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 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}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style: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);
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);

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
	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');

	Message('Number of Classes Processes: ',N1);
	Wait(1);
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);

 

 

Pat

 

Is there a version of the script that works in 2020 - 2023 ?

 

Appreciated

 

Peter

[Pat Stanford]

I thought I had already posted this, but here is the version that was modified for VW2019. In a very quick test it appeared to work fine in VW2023.

 

HTH.

 

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}
{Licensed under the GNU Lesser General Public License}

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);
SetObjectVariableBoolean(H1,82,False);	{Added 4/12/19 to hide database header rows.}
End;

Run(Classes_to_Worksheet);

 

[Elite Exhibits]

Pat

 

I am most grateful

 

Peter

 

 

[Pat Stanford]

You are very welcome.

 

You know about the magic trick that this worksheet does right?  😉

[Elite Exhibits]

On 4/10/2015 at 10:18 PM, Pat Stanford said:

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

 

 

Pat

 

¿ You are reference the database option ?

 

Let Me Know ...

 

Peter

[Elite Exhibits]

Pat

 

Please and Thank You

 

email me a current postal address and I send you a token of my thanks for your efforts

 

 

Peter

 

[Pat Stanford]

1 hour ago, Elite Exhibits said:

¿ You are reference the database option ?

Yes, I think the ability to get the sublist in a single worksheet instead of having to edit criteria of have multiple worksheets is kind of neat.  I'm glad I thought to try the criteria that made it work.

 

Pat