Worksheet of Classes in a File

[Pat Stanford 422]

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 422]

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 68]

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

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 422]

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 182]

Pat

This is really cool. Thanks!

MK

[Pat Stanford 422]

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 182]

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 422]

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 422]

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 182]

That's great.

[Kizza 7]

Very useful, thx.

[grant_PD 131]

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

[ibenjamin.price 2]

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

[bcd 166]

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.