AutoCAD VBA Programming

C h a p t er

Se v e n

Work i ng w i th Other Other Applications

T

he prima prima ry feature of VB is tha t it is capable capable of w orking orking w ith

other programs tha t support support au toma tion inside inside of Window Window s. Some of th e programs programs that support support autom ation include the

Microsoft Office 97 progra m s Word, Word, Excel, a nd Ac Access cess.. This This cha pter demo nstrates the ba sic sic strategies strategies of interfacing Auto Auto CAD VBA VBA w ith th ese ese tools. tools. In particular particular,, w e w ill ill pay attention to interfacing AutoCAD AutoCAD VBA VBA w ith Microsoft Excel. Excel. This This cha cha pter is merely a n in trodu ction to using au tom ation tools w ith th ese ese other packages. packages. There There are oth er books av ail-

able that deal w ith the interfaces into the packages in mu ch mo re detail. detail. This cha cha pter cont cont inues w ith an exploration exploration of th e techniqu es invo invo lved in building links links betw een Auto Auto CAD an d externa l data systems using using extended data (Xdata). We also cover the Dictionary and Xrecord objects in how they can be used used in relation relation w ith external interfaces interfaces..

Obj bjec ect Models odels Every program that supports ActiveX automation has an object tree that is sim sim ilar to t he Auto CAD object object tree. The The d ifference is th at th e tree for the o ther a pplic pplication ation w ill ill be spec specifi ificc to th at application application just as the AutoCAD object library is dedicated to AutoCAD. 167

A u t o c a d

V B A

PRO GR A M M I N G

The object mod el in in th e oth er application application s w ill follow th e basic strategy (figure (figure 7.1 7.1)) of h aving th e application application a t th e root. There There w ill ill be various properties properties,, meth ods, and events associated associated w ith the application application th at m ay interest interest yo u, but norm ally y our first first step is is into into the do cumen ts. Inside Inside the collecti collection on o f documen ts w ill ill be the one docum ent th e applic application ation w an ts to access. access. Each of the applications in Office 97 has specific names for each of the levels just just d escribed. escribed. In Microsoft Excel, a single single spreadsheet spreadsheet documen t is a w ork sheet. sheet. Work sheets are contained inside of Generic object object tree Figure Figure7.1: Generic

w orkbooks. orkbooks. Thu Thu s, w orkbooks are a t th e docum ents object object level, level, and w ork sheets sheets

are at the docum ent level. Inside Inside the w ork sheets sheets are cell cellss that a re the individual data items. When you’re talking to a spreadsheet or some other automation interface system system yo u w ill ill have to n avigate th rough th e object object tree tree to get at w ha t yo u w an t. This This is is not as difficult difficult as it it ma y soun d: it is just a m atter of learning learning the va rious rious branches branches and w hat they can do for you . The The on line help an d object object brow ser can be very h elpful elpful in locating these items. Before you can use the online browser to locate the items of interest, you must first attach the object models you want to use. You link to an object object library th rou gh t he referen ces setting located in th e Tools pulldow n m enu of VBAIDE. VBAIDE. Selecting Selecting th e References option in th e Tools men u brings up a men u o f th e ava ilable ilable object object references references.. You You might be surprised surprised at th e selecti selection on a va ilable. It’s It’s genera lly pretty obviou s w hich reference library library to u se w hen it comes to speci specific fic application s. For exam ple, ple, if you w an t to link in w ith Microsoft Microsoft Excel, Excel, page page throu gh th e list list to th e Microsoft Excel option, t hen pick pick it. The selected object object library w ill now become become a vailable vailable to your a ppli pplicati cation. on. If more tha n on e option option is ava ilable, ilable, pick pick the mo st recent recent edition edition (highest (highest release release n um ber). ber). To m an ipulate ipulate th e objects objects from th e oth er environm ents, you should have a good idea idea of w hat y ou w an t to do and how . That is, is, if if you don’t 168

A u t o c a d

V B A

PRO GR A M M I N G

The object mod el in in th e oth er application application s w ill follow th e basic strategy (figure (figure 7.1 7.1)) of h aving th e application application a t th e root. There There w ill ill be various properties properties,, meth ods, and events associated associated w ith the application application th at m ay interest interest yo u, but norm ally y our first first step is is into into the do cumen ts. Inside Inside the collecti collection on o f documen ts w ill ill be the one docum ent th e applic application ation w an ts to access. access. Each of the applications in Office 97 has specific names for each of the levels just just d escribed. escribed. In Microsoft Excel, a single single spreadsheet spreadsheet documen t is a w ork sheet. sheet. Work sheets are contained inside of Generic object object tree Figure Figure7.1: Generic

w orkbooks. orkbooks. Thu Thu s, w orkbooks are a t th e docum ents object object level, level, and w ork sheets sheets

are at the docum ent level. Inside Inside the w ork sheets sheets are cell cellss that a re the individual data items. When you’re talking to a spreadsheet or some other automation interface system system yo u w ill ill have to n avigate th rough th e object object tree tree to get at w ha t yo u w an t. This This is is not as difficult difficult as it it ma y soun d: it is just a m atter of learning learning the va rious rious branches branches and w hat they can do for you . The The on line help an d object object brow ser can be very h elpful elpful in locating these items. Before you can use the online browser to locate the items of interest, you must first attach the object models you want to use. You link to an object object library th rou gh t he referen ces setting located in th e Tools pulldow n m enu of VBAIDE. VBAIDE. Selecting Selecting th e References option in th e Tools men u brings up a men u o f th e ava ilable ilable object object references references.. You You might be surprised surprised at th e selecti selection on a va ilable. It’s It’s genera lly pretty obviou s w hich reference library library to u se w hen it comes to speci specific fic application s. For exam ple, ple, if you w an t to link in w ith Microsoft Microsoft Excel, Excel, page page throu gh th e list list to th e Microsoft Excel option, t hen pick pick it. The selected object object library w ill now become become a vailable vailable to your a ppli pplicati cation. on. If more tha n on e option option is ava ilable, ilable, pick pick the mo st recent recent edition edition (highest (highest release release n um ber). ber). To m an ipulate ipulate th e objects objects from th e oth er environm ents, you should have a good idea idea of w hat y ou w an t to do and how . That is, is, if if you don’t 168

C H A P TE R S E V E N :

Working w ith Oth er Appli Applications cations

know how to use the Excel Excel spreads spreadsheet heet system, system, y ou w ill ill have difficulty difficulty understanding understanding a ll that that you can do w ith the objects objects that are ava ilable ilable.. It w ill ill be be w orth you r w hile hile to spe spend nd time learning learning how to ma nipulate nipulate the system ystem in its native mode in order to gain an apprec appreciati iation on of w hat the tool can do for a n a pplic pplication ation . The The objects objects and m an ipulation ipulation tools ava ilable ilable to ActiveX ActiveX are generally generally q uite extensive, and you r application application programs programs can ta ke advanta ge of virtually virtually every every feature of the oth er softw are system. system. For you to have access to these tools they must be installed on the com puter y ou a re using. using. Tha Tha t is, is, yo u can no t possibly possibly access access th e object object mo del of Excel w itho ut Excel first first being installed. For object object mod els to w ork, the parent task or host mu st be available to servic servicee the req uests. uests. The sam e is true of AutoCAD and an y other program of such sophisti sophistication. cation. The The term a utom ation m eans just just that — auto ma tion of th e tasks on yo ur computer so it does them m ore q uickly uickly an d efficiently efficiently..

Inte nterface to to Microsoft oft Excel This secti section on w ill ill show show you how to interface to th e Microsoft Microsoft Excel Excel spreadsheet system of Office 97. Excel is a spreadsheet system, so it is exceptional at manipulating tabular data. AutoCAD-based applications can take advantage of Excel to build reports based on information found in th e draw ing. At At first first it ma y seem like like ma gic gic to run run a program program from an other program, but once you get used to objectobject-oriented oriented programm ing of this na ture it seems seems primitive primitive to do it an y other w ay. The Excel object object t ree starts a t th e Excel application (figure 7.2). Given an object object pointer to th e application, application, y ou access access the w orkbook object object to get to th e w orksheets level. level. From From t here y ou select select

169

Figure Figure7.2: Excel object tree

A u t o c a d

V B A

PRO GR A M M I N G

an in dividual w orksheet orksheet an d a ran ge man ipulated ipulated inside inside of that sheet. A ran ge can be a single single cell or m ultiple cells cells inside inside th e sheet. There There a re nu merous meth ods, events, events, an d properties properties associated associated w ith each of th ese ese levels levels in in t he system tha t y ou can explore explore using using th e online help an d object object brow ser ser systems. systems. We We w ill ill now turn o ur atten tion to th e details involved w hen interfacing Excel Excel an d AutoCAD using using VBA. VBA. Interfacing Excel to AutoCAD AutoCAD can result result in elegan elegan t solutions to common problems found in the design and drafting field. Using the strengths strengths of ea ch system yo u could con struct struct a bill bill of m aterials system system tha t count s blocks blocks or oth er tagged objects objects in a dra w ing by building a spreadsheet spreadsheet detailing detailing w ha t w as foun d. The The spreadsheet spreadsheet can th en be further instructed to calculate calculate th e costs costs associated associated w ith th e values found. The n ext step w ould be to rea d th e spreads spreadsheet heet conten ts back into th e draw ing to create create a bill of ma terial table table inside inside the draw ing. A variation of the same idea w ould be the construction construction of ho le charts in in a spreadsheet spreadsheet based on th e locations of circl circles es foun foun d in th e draw ing. Once in the spreadsheet, locations can be sorted and other data appended to the report to finish it quickly. Even if you’re not an advanced systems programm er, er, you can en ha nce productivity productivity by merging merging the pow er of a graphics system such as AutoCAD and a data table manipulation tool such as Excel.

Excel Appl pplicati ation 1– Simpl ple e Datab atabas ase e Let’s Let’s start w ith a sim sim ple application application exa mple. Excel serves serves excellently excellently a s a sim sim ple da ta base for inform at ion ba sed on ta bles of properties. Tha t is, is, yo u use Excel Excel to keep track of various constants indexed by a key na me. This This is a simple spreadsheet of the most basic nature, in which each row in the spreadsheet spreadsheet repres represents ents a record a ssociated ssociated w ith th e key n am e stored in one of the columns (typically the first column, but it doesn’t have to be). We w ill ill use a simple simple spreadsheet spreadsheet of th is style to demo nstrate h ow to obtain information from Excel via a VBA program running inside AutoCAD. Obviously, the main advantage of doing this is that the end

170


Working w ith Oth er Appli Applications cations

user can maintain the table of data using a table-editing tool (Excel) and does not ha ve to w ork in in a t ext editor or in in th e source source code code of an AutoLISP routine. The follow ing is an exa mple spreads spreadsheet heet th at ha s the first first column as a key n am e an d th e next tw o ent ries ries as scalar scalar va lues. lues. These These scalars scalars could could represent dimensions, tolerances, physical properties, or some other data of int erest to our a pplication pplication . The The goa l of this exam ple is is to access access them by name and retrieve the values from the spreadsheet, thereby demon strating th e basic basic strategies strategies of opening a spreadsheet preadsheet a nd obtaining information from one via VBA. In th e follow ing spreadsheet spreadsheet (figure (figure 7.3) 7.3), columns a re labeled labeled w ith letters (A (A,B,C) a nd row s are nu mbered (1,2,3,4, (1,2,3,4,5) 5) just just as foun d in Excel.

EXAMPLE1.XLS– spreadsheet content contentss A

B

C

1

Na m e

D im 1

D im 2

2

AA-1

1. 5

1. 5

3

AA-2

2. 5

1 . 75

4

AA-3

3. 5

1 . 85

5

AA-4

4. 0

2. 0

Figure Figure7.3: Example 1XLS— spread sheet contents

You w ill ill create create th e XLS XLS file file ah ead o f time u sing no rma l Excel Excel operation s. The file na me t o use for ou r exa mple is “ EXA EXAMPLE1.X MPLE1.XLS” LS” a nd t he sheet name will be “Sheet 1”, the default. You can use normal Excel operations to define define th is table in the system system if you w an t to try it yo urself. urself.

Link Li nk to to Excel The rem ain der of th e program m ing ta kes place inside inside VBA VBA in AutoCAD. When yo u’re starting a new project, project, the first first step is is to link the Excel object library library using th e Too ls-Res ls-Resou ou rces men u selection selection w ith t he pro ject. ject. The specific specific library to select w ill va ry depen ding on w ha t version of Excel you have installed. For example, the name may appear as Microsoft Excel 171

A u t o c a d

V B A

PRO GR A M M I N G

8.0 Object Library if you ha ve Office 97 installed. Now ou r VBA program w ill know how to talk w ith Excel. (Remember that you m ust have Excel installed on you r comput er to access the library.)

VariableDeclarations After adding the linkage references for Excel, the next step is to start coding the interface. After inserting a new mod ule into the project, w e type in the follow ing declarations. Dim ExL As Object Dim XLWorkBook as Object Dim excelSheet As Object

We use generic object cont ain ers for th ese va lues w hen interfa cing to Microsoft Excel. You can use the object types from the Excel library as w ell (w hen a vailable). When u sed as show n, th e objects are bound to th e Excel objects at run tim e. This is ca lled late bindin g in programming. If you use the definitions of specific objects from the Excel class, the object types are considered early boun d . Early binding helps applications run faster, as the system does not do th e binding w ork w hen t he user is at th e helm. Instead, in early binding th e linkage is done w hen the a pplication is built, hen ce the system know s how to ma nipulate the references right aw ay. The problem w ith early binding is tha t the va riables defined in this ma nner canno t be used to h old any other types of objects if that w as desired later in th e program. Since this application does not va ry th e ty pes of objects held in th e object references, w e could also ha ve coded the declarations as follow s. Dim ExL As Excel.Application Dim XlWorkBook As Excel.Workbook Dim excelSheet As Excel.Worksheet

The primary adva nta ge of using ea rly binding is tha t th e system can assist y ou in ty ping in object references. As yo u ty pe the va riable nam e used, a list box o f ava ilable selection s w ill appea r. When th e generic 172


Working w ith Oth er Applications

object definition (late binding) is used, the system do esn’t kno w w ha t kind of object y ou a re referencing during the coding a nd ca nn ot possibly assist in the typing. These definitions are global to o ur m odule, an d y ou can access them by any of the subroutines or functions w e include in th at m odule. If w e w anted to make these variables available to other modules, w e w ould chan ge the Private statem ent to Pu blic. This allow s the va riables to be addressed as part of our module (named Module1 by default) by other source code m odu les. The v isibility, or scope, of va riables from on e mo dule to a no ther is covered in mo re detail in a later chapter. If speed of execution is an issue, and it no rma lly is, be aw are th at run nin g Excel from in side Auto CAD w ill not be a s fast as if the same VBA mo dule w ere runn ing inside Excel. Within larger application s, yo u ma y w an t to h ave th e VBA in Excel handle the Excel side of the problem a nd ha ve th e VBA in AutoCAD simply lau nch the m acro in Excel. The rea son is th at the VBA code inside Excel is tightly coupled to Excel w hile the VBA code runn ing inside Auto CAD is boun d at run time w ith Excel. Tha t mea ns tha t every tran saction tha t takes place betw een AutoCAD and Excel must go through a series of interfaces before getting to the other application. For simple man ipulations this w ill not be an issue, but w hen w orking w ith more advanced applications the time savings could be significant.

Excel LinkageFunction Now w e need a function tha t w ill start Excel an d assign the object to the variables dimensioned a bove. Staying in the code w indow for the m odule just started, w e type the follow ing line of code. Sub Set_Up_Excel

VBA fills in th e rest for us, addin g in a new subroutine d efinition to the m odule. The pa rentheses are a dded, a s is the End Su b statemen t. All w e hav e to do is fill in th e middle part. When y ou ty pe in th e Sub statement w ith the nam e of a new function, the code editor w ill contain th e follow ing after y ou press the Enter key. 173

A u t o c a d

V B A

PRO GR A M M I N G

Sub Set_Up_Excel () End Sub

The follow ing is w ha t w e filled in for th e function . This function w ill start Microsoft Excel w ith th e “ EXAMPLE1.XLS” w orkbook (mu st be located in th e default Excel document directory) an d “ Sheet1” w orksheet loaded an d ready to go. Each line of the code is explained in th e text th at follow s. All the code is presented t ogeth er for tho se just scan ning for examples. Sub Set_Up_Excel() On Error Resume Next Set ExL = GetObject(“”, “Excel.Application”) ExL.Visible = True If (Err.Number <> 0) Then Err.Clear MsgBox “You must have Excel loaded on your computer!” Exit Sub End If Set XLWorkBook = Workbooks.Open(“EXAMPLE1.XLS”) Sheets(“Sheet1”).Select Set excelSheet = ExL.ActiveWorkbook.Sheets(“Sheet1”) End Sub

The first part o f the progra m w ill at ta ch th e Excel application o bject. GetObject() is a VBA function that retrieves an application object given the executa ble file nam e or th e class nam e of th e application. If Excel is running, the class name “Excel.Application” will find a link and return the a pplication object for our program to u se. If Excel is not run ning, th e GetObject() fun ction w ill start Excel. If Excel is not ava ilable on the com puter, the program w ill fail. Tha t’s w hy the error ha ndler is turned on before the call to G etObject(). If the error ha ndler w as not en abled an d Excel w as not a vailable, the program w ould crash in an ugly m ann er.

174



This w ay w e can con trol the exit situa tion a nd issue a proper error message tha t m akes sense to th e user. By including th e empty string as the first param eter to th e getObject() function, w e are instructing VBA to run Excel if it is not already runn ing in th e computer. Leaving th e first para meter blank w ill cause an error if an instance of Excel does not already exist in the machine. Anot her w ay to access the w orkbook object is throu gh the createObject() fun ction. This fun ction beh av es just like getObject() except tha t it w ill start th e application . If your a pplication know s it w ill be starting Excel w hen it is going to run , then the createObject() function is a better choice. The n ext step is to open th e w orkbook using th e Open meth od of t he Workbooks object in Ex cel. You ’ll supply th e complete file na me, w ith extension, to the Open function. No directory was specified in the example routin e. The file is assumed to be in th e Excel or system search pat h. If it’s not in the search path , yo u’ll need to provide a com plete path n am e. The extension XLS w ill not be appended au tom atically, w hich mea ns tha t your application can use alternative extensions as a minimal form of data security. The result of a successful o pen ca ll is a w orkbook object. Workbooks conta in w orksheets, so t he n ext level into th e object tree takes our a pplication to th e specific sheet w e w ish to h ave a vailable. The example program selects “Sheet1”, the default sheet name in a new w orkbook. The w orksheet o bject is then assigned to the variable excelSheet. The result of runn ing this fun ction is that th e w orkbook and w orksheet are opened and their respective object references are stored in the variables defined in the declara tion section.

Closing the Workbook Something to remember for later coding is that after you have opened and manipulated a workbook, you should close it using the Close meth od. The w orkbook object close metho d releases the w orkbook for

175

A u t o c a d

V B A

PRO GR A M M I N G

oth er applicat ions (including Excel) to access. The follow ing subrou tine show s how to close a w orkbook. Sub Close_book () XlWorkBook.Close End Sub

Searching the Worksheet The fun ction w e need now is one th at searches the w orksheet to find a ma tch. Specifically, w e are interested in searching for someth ing in on e of the column s. In this application w e w an t to search th e spreadsheet for a ma tch w ith a key n am e in column one. That mean s that the a pplication w ill look for mat ches w ith va lues like “AA-1” , “ AA-2” , an d so forth. When a ma tch is found, w e w ant th e values from the next tw o column s. To locat e a pa rticular cell in a spreadsheet w e can u se th e Find () meth od th at is associated w ith a ran ge object in Excel. A ran ge object defines a section of th e w orksheet. Worksheet ra nges are defined by ro w an d column location such a s A1, w hich represents tha t single cell located at column A, row 1. A range can specify more than one cell, such as a column of cells in the spreadsheet designated as A1:A10 for column A, row s 1 through 10. Ranges are typically rectangular an d can span mu ltiple row s and colum ns as in A1:B3, w hich represents the cells found w ithin columns A and B from row 1 through 3. Given a range of cells, you can apply the Find() method to look for a ma tching value. The find m ethod w ill return a ra nge of cells tha t ma tch th e find req uirem ent s. The resulting ran ge can be a single cell or a grou p of cells. When an application is dealing w ith a list of u niqu e key na mes, there should be only one match. If there is a situation in which Find() could locate m ore than one, then a ran ge is returned that y ou can traverse using subseq uent calls to th e FindNext() metho d. Each call to FindNext() w ill return th e next cell foun d to m atch the search criteria. When there are no more selections, FindNext() is null. Our application example expects the key names to be unique, meaning that no key is repeated, so the FindNext() loop is not required. 176



The va lue return ed from th e find met ho d is a Ra nge object. The address method of the Range object is used to obtain the location of the cell that w as located a nd is returned a s a string. When the a ddress meth od is used, a dollar sign w ill proceed each part of th e address so that if a match was made with column A and row 3, the result would be $A$3. You can chan ge the response of th e add ress method by u sing th e variable parameters; how ever, the default approach is good eno ugh for our purposes. When you are searching a table using a specific column, you know the column value and can remove it from the result string to extract just the row number where the match was made. In our example, we are searching the A column for a key n am e match a nd can thus remove the first three chara cters of th e resulting address (the “ $A$” part) to obta in on ly th e row num ber (3). Now if our application w ants to o btain the va lue of a particular column member of that row, it can access the Cells().Value directly. The follow ing code segment searches the open w orksheet saved in the object variable excelSheet. It searches column A betw een row s 1 and 10 to find a m atch w ith the value “ AA-3”. When the ma tch is found, the data value found in column B is accessed and placed in the variable Resulting_Value. Dim Fnd As Excel.Range Set Fnd = excelSheet.Range(“A1:A10”).Find(“AA-3”) R$ = Fnd.Address Rw = Val(Mid$(R$, 4)) Resulting_Value = excelSheet.Cells(Rw, 2).Value

To search th e entire colum n labeled a s A, yo u can specify the ra nge a s (“A:A” ) instead of limiting th e range search to row s 1 through 10 as in the code a bove. In Excel, this format is considered “A1” not ation . Most spreadsheet users are used to den oting a reas or ranges in t he docum ent w ith “ A1” nota tion. “ A1” not ation uses letters to represent th e colum ns and num bers to represent the row s. In this notation the va lue B5 w ould refer to the fifth entry in the second column. 177

A u t o c a d

V B A

PRO GR A M M I N G

When using the “A1” notation, you can shortcut the code entry by just specifying t he desired ra nge inside of sq ua re brackets as in [A1:A10] instead of Ran ge(“A1:A10”). Note tha t th e qu ota tion m arks are not needed when defining the range using the shortcut approach. There a re oth er w ay s to deno te ran ges of cells inside Excel from VB A. An ea sy alternative is to use the Row s and Colum ns properties of a w orksheet. You can use these properties to define a ra nge based on an entire row or column . Using the column property, you w ould rew rite the code as follow s. Set Fnd = excelSheet.Columns(1).Find(“AA-3”)

The en tire first colum n o f th e spreadsheet is search ed to loca te th e desired m atch. The Colum ns() property of th e w orksheet returns a ran ge and that satisfies the Find method. There is an oth er w ay to search a spreadsheet for som e informa tion: you can use the Cells() property w ith index values of the row an d column. You cou ld change th e exam ple search to read a s follow s. R$ = “” For I = 1 To 10 If excelSheet.Cells(I,1).Value = “AA-3” Then R$ = excelSheet.Cells(I,2).Value I = 11 ‘break out of the loop End If Next I

At the end of th e loop, the variable R$ w ould either hold the value from the second column of the row in w hich th e “AA-3” m atch w as made or it w ill be an empty string. So w hich style shou ld your a pplication use? Use w ha tever makes sense for the a pplication at h an d. The Find() meth od w ill locate a m atch inside of a range faster than a direct iteration loop; however, it is limited to finding exact m atches. If you n eed to test values to see if they fit a particular range for a selection to be made, then the direct iteration approach will

178



probably w ork better for the a pplication . An exa mple is a program tha t searches a database of available sizes and selects the one nearest in size (but larger than ) a th eoretically ca lculated size. In th is case, there m ay not be an exact match, and some logic must be applied to the spreadsheet data in order to make a selection. For most applications, you apply a specified range and an exact value (often called a key) for the search. In th ose cases, the “ A1” n otat ion style w ill w ork nicely in conjunction w ith a Find(). The ra nge to search could be ha rd coded as in th ese exam ples, or it could be supplied in the form of a range variable. And because strings can also be stored inside a spreadsheet, there is not hing tha t w ould stop an a pplication from obtaining th e ran ge value to use from some oth er cell in th e spreadsheet. When yo u’re searching th rough a spreadsheet, the best solution for any given application to use is the one that reads in a logical manner. For example, if someone said to search the first column, then the Columns() property seems to make the most sense. On the other hand, if someone specified tha t th e application shou ld search th rough a specific set of row s an d columns, then th e “A1” no tation w orks best. And if there is a n eed to perform calculation s or com parisons w ith th e values, a direct iteration is the only w ay to get the job done.

Another ExampleInterfacetoMicrosoft Excel Simple exam ples demon strate h ow easily yo u can use Excel to create reports from AutoCAD draw ing informa tion. This function w ill create a hole chart from all the circles foun d in th e draw ing. A hole chart is a table tha t lists holes foun d in the dra w ing along w ith their X-Y location. The h ole chart is typically used for drill program min g or for locating bolted attachments in an assembly. This simple fun ction set w ill create a chart w ith sequent ial hole nu mber, X, Y location of center point , an d th e radius of th e hole. The h ole num ber will then be w ritten back into th e draw ing at the center point of the circle using the current defau lt text h eight. This exam ple demon-

179

A u t o c a d

V B A

PRO GR A M M I N G

strates just how simple it is to build a pow erful reporting too l for AutoCAD w ith Excel. The a pplication is broken dow n int o small mod ules to m ake th e code easier to read. The first listing conta ins the globa l variable declara tion s for the m odule. There are tw o va riables that w e w ill declare as global (ava ilable to all functions and subroutines in the module), and they are a link to the active Excel spreadsheet and an AutoCAD selection set collection. Dim excelSheet As Object Dim SS As AcadSelectionSet

The m ain program m acro is called Holes and it is defined in the fo llow ing listing. The Holes fun ction calls oth er fun ctions tha t perform t he detail operations. First th e Excel object is foun d a nd linked. (Note th at w e have removed the normal error checking from this example to keep the code simple and brief.) Once the link ha s been established w ith Excel, th e function Get_Circles is called to obtain a selection set collection of all the circles found in th e draw ing. The collection is then u sed by the subrout ine Send _Holes_To_Excel th at read s each object a nd places the coordinates and radius in a spreadsheet. We sort th e spread sheet using th e Sort() m etho d of Ex cel. The So rt() meth od implements the stan dard sorting ava ilable in the Excel system. In this application w e w ill sort by the X, Y, an d Radius va lues in a scending order. After the sort, the spreadsheet data is read ba ck into the program in sorted order. A text note is then placed at each hole indicating its position in th e sorted list, and th e ha ndle is replaced w ith the h ole num ber in the spreadsheet. When this function is completed, th e ho les w ill be labeled in AutoCAD and a supporting report is ready to be finished in Excel. Sub holes() Dim Excell As Object Set Excell = GetObject(, “Excel.Application”) Set excelSheet = Excell.ActiveWorkbook.Sheets.Add Set SS = Get_Circles Send_Holes_To_Excel

180



excelSheet.Range(“A1”).Sort _ key1:=excelSheet.Range(“B1”), _ key2:=excelSheet.Range(“C1”), _ key3:=excelSheet.Range(“D1”) Set_Hole_Numbers End Sub

The Sort() metho d can be a tad con fusing a t a q uick glance. From t he exam ple above, it appears as thou gh the sort w ould do on ly a single cell—certainly not the desired operation. Actually, the value supplied is either a ran ge or the first cell of a region. In this case, w e are sorting a region th at is designa ted a s sta rting a t “ A1” . The sort fields are defin ed by assigning the para meter va riables Key1, Key2, an d so forth to the first sort fields in th e spreadsheet. For ou r exam ple w e are sorting by the X, th en Y, then th e radius valu es. You ca n sort th e fields in a scendin g (the defau lt) order or in descendin g order. To h ave a field, such a s the Y values, sort in descending order, use the Order2 parameter va riable and set it to a value o f xlDescending. Order1 w ill chan ge the sort order of the first key field, a nd Order3 w ill chan ge the o rder of th e third set. There are u p to three sort fields that can be defined in the Sort() method. Let’s turn ou r atten tion to the first of th e subroutines called from t he ma in fu nction . The G et_Circles function w ill build a selection set collection of circles found in the drawing. Function Get_Circles() As AcadSelectionSet Dim Cir1, Cir2 As Variant Dim CirA(0 To 0) As Integer Dim CirB(0 To 0) As Variant CirA(0) = 0 CirB(0) = “CIRCLE” Cir1 = CirA: Cir2 = CirB On Error Resume Next Set Get_Circles = ThisDrawing.SelectionSets.Add(“HOLES”)

181

A u t o c a d

V B A

PRO GR A M M I N G

If Err.Number <> 0 Then Err.Clear Set Get_Circles = ThisDrawing.SelectionSets.Item(“HOLES”) End If Get_Circles.Clear Get_Circles.Select acSelectionSetAll, , , Cir1, Cir2 End Function

The va lue tha t w ill be return ed from t he G et_Circles fun ction is a selection set collection. The fun ction is defined as being o f th e ty pe selection set, and w e can use the name in our program as a variable to house th e selection set collection w hile it is being constructed. G et_Circles uses a filter to con struct th e selection set collection. The filter is to look for circles only and can be expanded to include layer names and other criteria as w ell. Given the selection set collection, the n ext step is to w rite the va lues out t o Excel in the w orksheet already started. At the beginning of th is subroutine, the w orksheet is expected to be em pty. Sub Send_Holes_To_Excel() Dim Ent As AcadCircle CN = 1: CX = 2: CY = 3: CR = 4 R = 1 Dim PTV As Variant For Each Ent In SS excelSheet.Cells(R, CN).Value = Ent.Handle PTV = Ent.Center excelSheet.Cells(R, CX).Value = PTV(0) excelSheet.Cells(R, CY).Value = PTV(1) excelSheet.Cells(R, CR).Value = Ent.Radius R = R + 1 Next Ent End Sub

182



This function loops throu gh th e selection set collection (stored in global variable SS, w hich w as set in the ma in program a s the result of calling Get_Circles). Each entity is assumed to be a circle object, meaning that certain va lues are know n to be ava ilable such as the center point a nd radius. As this subroutine reads through the selection set collection each ent ity is placed in th e varia ble Ent for processing. The first column of th e spreadsheet is set to th e entity h an dle. Han dles are the best w ay to link entity objects w ith externa l data structures; w e w ill be discussing th em in mo re detail later in th is chapter. The second an d th ird colum ns of th e spreadsheet are th en set to the X a nd Y values of th e circle center point. The fou rth colu mn is set to the ra dius value fou nd in t he circle object. The va riable R holds the row nu mber as the program a dds each hole location to the spreadsheet. After the user w rites the h ole location an d size informa tion to the spreadsheet, th e subroutine en ds and control is returned back to the main program. The m ain program th en sorts th e spreadsh eet as a lready discussed. The sorted spreadsheet contains the data sequenced by X, Y, and Radius values. The n ext subroutine w ill read th e spreadsheet an d place hole nu mbers at each circle location . There are tw o operation s that w ill take place as this fun ction itera tes throu gh th e spreadsheet. The first is to place the h ole nu mber in the dra w ing. The secon d is to replace the h an dle entry in th e spreadsheet w ith the seq uentia l hole nu mber. Sub Set_Hole_Numbers() Dim PTV As Variant Dim PTC(0 To 2) As Double I = 1 TH = ThisDrawing.GetVariable(“TEXTSIZE”) While excelSheet.Cells(I, 1).Value <> “” PTC(0) = excelSheet.Cells(I, 2).Value PTC(1) = excelSheet.Cells(I, 3).Value PTC(2) = 0# ThisDrawing.ModelSpace.AddText Str$(I), PTC, TH

183

A u t o c a d

V B A

PRO GR A M M I N G

I = I + 1 Wend End Sub

The fu nction begins by obta ining th e current defau lt text size for th e draw ing. This value w ill be used w hen adding th e text objects for the ho le num ber. A While loop is started t ha t w ill iterate so lon g as the cell in the first colum n of the current row nu mber (in va riable I) ha s a n onblank entry. Han dles are never blan k, thus w hen th e program en counters a bla nk cell, it ha s hit the en d of th e list. This version o f th e fun ction doesn’t do anything with the handle other than test to see if one is there. The X an d Y va lues for the cent er of th e circle are retrieved fro m t he spreadsheet an d placed into a n array of dou bles. This array is set into the va rian t va riable PTV w hich is needed by th e addC ircle() function . You mu st u se va riants w hen sending a nd getting points from objects. The reason h as to do w ith the w ay the BASIC language (as implemented in VBA) passes para meters interna lly a nd t he fa ct tha t a rray references are handled better using a variant pointer. The row nu mber is incremented, a nd the loop continu es un til the last hole has been read and the text placed in th e draw ing, at w hich point the subroutine finishes. This function set demon strates the basics an d perform s a very useful operation as w ell (shou ld you n eed hole cha rts an d a n um bering system for ho les). VBA provides a pow erful w ay to tie various tools together so that each can be used in its ow n w ay to ma ke the application dream a reality.

Using Handles The last exam ple used han dles but didn’t really do a ny thing w ith them other than look for a blank handle indicating the end of the list in the spreadsheet. Handles are strings that un iquely identify each object in a draw ing. AutoCAD assigns hand les as the objects are created a nd n ever reuses the same h an dle in the draw ing. There a re tools in AutoCAD’s 184



programm ing systems for converting h an dles to en tity objects. As such, ha ndles present a w ay tha t entity object references can be mov ed to an external system such as a spreadsheet for later reference. In VBA, a handle is converted to an entity object through the HandleToObject() met ho d. This meth od is associat ed w ith t he d ocum ent object and can return values only w hen th at document is the current document. Tha t is, y ou can get ha ndles conv erted into objects only in th e currently opened drawing. Using this conversion ut ility you can upda te the draw ing based on the values in th e spreadsheet as in the follow ing exam ple, w hich looks at th e same spreadsheet. In th is subroutine, the v alues in the spreadsheet a re read one at a time and the radius values checked against the original draw ing objects. If cha nged, the draw ing object is updated a nd th e lay er chan ged to lay er “ CHANGED” . The lay er is assum ed to exist before the macro is run. Sub update_radii() Dim Excell As Object Set Excell = GetObject(, “Excel.Application”) Set excelSheet = Excell.ActiveWorkbook.Sheets(“Sheet32”) Dim Ent As AcadCircle I = 1 While excelSheet.Cells(I, 1).Value <> “” HN$ = excelSheet.Cells(I, 1).Value Set Ent = ThisDrawing.HandleToObject(HN$) If Ent.Radius <> excelSheet.Cells(I, 4) Then Ent.Radius = excelSheet.Cells(I, 4) Ent.Layer = “CHANGED” End If I = I + 1 Wend End Sub

185

A u t o c a d

V B A

PRO GR A M M I N G

Object IDversusHandle An alternative to handles are Object ID values. Object ID values use less storage space compa red w ith ha ndles (in m edium an d larger draw ings). An Object ID is a lon g integer va lue instead o f a string. Thu s, once th e entity handle names exceed four characters in length, the length of a ha ndle is longer tha n t he object ID storage space. Anoth er attra ctive feature of Object ID values is that they are integers an d can be compared more quickly than character strings. Object ID values are mostly used in ObjectARX applications and are available to VBA programmers for that purpose. ObjectARX applications use the object ID va lues w hen con necting w ith th e various objects in the draw ing databa se. Some of the VBA utilities an d event ha ndling functions supply o r requ ire object ID values as w ell. To get a n o bject ID va lue from an existing object, u se the ObjectID property o f the o bject. If w e ha ve an entity object stored in th e object variable Ent, th en th e expression Ent.ObjectID w ill return th e object ID value. Object ID values are read-only and cannot be set by an application. You can convert object ID values to objects via the utility method ObjectIDtoObject() available in th e current dra w ing da taba se. This method is used in a manner just like the handle conversion utility to obtain the object. Also, like the handle conversion utility, the object ID conversion utility can be used only in th e current draw ing to obtain an entity object. Tha t is, if you ha ve an other draw ing open (from the docum ents collection ) you can no t access the objects inside w ith just an object ID v alue. The problem w ith object ID va lues is tha t th ey w ill change a s the draw ing is saved an d reloaded. Tha t is, they are n ot persistent inside th e draw ing file. Handles never chan ge in a draw ing file; once used, a han dle is nev er repeated. This ma kes ha nd les th e ideal to ol for linking Auto CAD draw ing objects w ith ou tside applications. Object IDs are n ot w ell suited for that sort of interface unless the application will not exit the drawing during the life span of the external data.

186



Linkingwith Other Applications Linking graphic data in AutoCAD w ith param eters an d associated data bases requires you to think carefully about the tools involved and which on e does w hich job best. AutoCAD is obviou sly superior in graph ics ma nipulations w hen compared w ith applications such a s Word or Excel. On the oth er han d, these other applications manipulate w ords and data charts much better than AutoCAD. At first it w ould appear tha t the delinea tion betw een the different application s is pretty cut a nd dried. This is no t th e case. Excel is very goo d at making charts as well as manipulating tables. At the same time, AutoCAD includes custom dictiona ry objects for storing ta bles of da ta internal to the drawing. And Word provides table-generating features that make for very goo d charts an d m aterial lists. So how do you know w hat w ay to turn w hen looking at integrated applications of this na ture? G enerally, the best answ er is to consider w ha t needs ma nipulating an d w hat tool is best a t tha t m anipulation. Con sider an application in w hich a da ta ta ble conta ining a set of standa rd sizes is referenced. Storing that table inside each and every AutoCAD drawing may seem ludicrous at first glance. If someone needs to change the standard values, he or she might ha ve to change them in every draw ing. It w ould be much easier to cha nge them in a spreadsheet or databa se, then ha ve the new er values ava ilable for all draw ings. How ever, suppose that the standards are changed every so often but that an associated application must work with the standards that applied when the drawing was created. Under th ose circum stances, it m ight be better to store the data table inside the draw ing or at least in a da taba se closely associated w ith just that draw ing. The circumstan ces of the a pplication an d ho w the da ta w ill be needed w ill greatly influence w ha t too l is used to accomplish t he job. For m ost applications, the clear delinea tion betw een AutoCAD as the graph ics engine, Excel as the table engine, a nd Word a s the reporting en gine is enough to get started. In general, the more you know about a particular

187

A u t o c a d

V B A

PRO GR A M M I N G

application, th e mo re you w ill be able to exploit its abilities w hen interfacing AutoCAD w ith it. The reason is that th e sam e softw are tha t services the operator comm an ds processes the aut oma tion to ols yo u can use. Objects are a pow erful w ay for one application to link up w ith another.

StoringDatainAutoCAD So far w e’ve looked at ha ndles as a w ay to store a reference to an AutoCAD entity object in an other database. But w hat about linking some oth er data base’s key in a n AutoCAD draw ing? Since a key is typically text, it could be stored as a very sma ll text object in the dra w ing. But keys are not graphic objects, a nd it is not desirable to ha ve text o bjects cluttering u p the draw ing. To a id applications facing th is dilemma , AutoCAD has some other mechanisms for storing non-graphical information in a draw ing. There are tw o tools that can be used to store non-graphical informa tion inside a dra w ing. The first is a dictiona ry, w hich can hou se lists of objects. The secon d is extend ed da ta . The prim ary difference is tha t extended da ta is atta ched to existing objects in a draw ing, so it is limited in the am ount of data that can be added. Dictionaries can contain independent objects, a nd these objects can be of a ny size.

Dictionary Object A dictionary is a collection of objects; it can contain any objects the application requires. Dictionary objects are members of the dictionaries collection , a s w e discussed in C ha pter 6. The o bjects tha t m ake u p a dictionary can be of our ow n custom design (you mu st use ObjectARX to create a truly custom object). Dictionary objects can also be other AutoCAD draw ing objects such as lines, a rcs, a nd circles. In this ma nn er, a diction ary can be used to store a selection set betw een edit session s, mu ch like a group but w ithout a ny operator control options. Lastly, there is an object available in AutoCAD that is found only in dictionaries 188



called an Xrecord. Dictionaries can be made up of Xrecords or any combination of entity objects.

Xrecord Objects When interfacing w ith externa l systems or using custom design softw are, Xrecords present a n interesting m ethod o f storing data inside a dra w ing that is not directly attached to an entity object. An Xrecord is a nongraphical object that can contain any number of parameters, just like a graph ic object. Tha t m ean s you ca n store point s, strings, integers, real numbers, and so forth. Xrecords are located using a dictionary and a key na me. The key n am e is a string tha t is stored w ith th e Xrecord inside the dictiona ry. Since your a pplication stores the key n am e, it should a lso know how to retrieve it for later use. Xrecords provide a w ay to store the values of various variables tha t might be used in an application. A program can w rite Xrecords con taining th e valu es of key va riables in respon se to a sa ve-event ta king place. Tha t w ay, w hen the program is started again in the same draw ing at a later time, th e variable values can be retrieved.

AccessingaDictionary Creating a dictiona ry o bject in VBA is simply a m atter o f adding a new mem ber to the dictiona ry collection of th e draw ing. When th e draw ing is saved, the dictionary is saved w ith it. The follow ing code segment w ill create a new dictionary o bject in th e current draw ing. Dim Dict As AcadDictionary Set Dict = ThisDrawing.Dictionaries.Add( “My Dictionary”)

You n eed to create a d ictiona ry o bject only o nce; doing so a second time w ill result in a n error trigger. If a particular dictionary object already exists in th e draw ing, yo u’ll access it using the Item() meth od. The follow ing code w ill access the dictiona ry object nam ed “M y Dictionary ” an d if it is not foun d, w ill create it.

189

A u t o c a d

V B A

PRO GR A M M I N G

‘ Dictionary objects should be global Dim Dict As AcadDictionary ‘ On Error Resume Next Set Dict = ThisDrawing.Dictionaries.Item ( “My Dictionary”) If Err.Number <> 0 Then Err.Clear Set Dict = ThisDrawing.Dictionaries.Add(“My Dictionary”) End If

The dictiona ry o bject can be u sed to reference the objects w ithin t he diction ary. Wha t yo u w ill find in a diction ary is entirely up to th e application tha t ma intains the dictionary. In mo st cases, the dictiona ry w ill con ta in object references. The o bjects in the diction ar y a re accessed just like a collection in th at y ou u se na mes or the index nu mber to obta in the reference. For most applications the dictionary serves as a w onderful place to store variable values an d param etric data . A dictiona ry can also be used to store tables of data reflecting standa rds in effect w hen th e draw ing w as created. As a result, the most common member of a custom dictionary is the Xrecord. Because Xrecords can contain any data and make use of the same n um bering system as AutoCAD objects, they a re very easy for m ost AutoC AD program mers to u se.

XrecordContents An Xrecord conta ins variable data: o ne Xrecord does not ha ve to look like th e next in t he diction ary collection . The da ta fo r an Xrecord is supplied in tw o a rrays. The first a rray conta ins integer group codes that specify th e kind o f data tha t w ill be foun d at t he same offset in th e second arra y. The second a rray is of type variant, mean ing that it can conta in an ything. When accessing o r w riting a n Xrecord, these tw o a rrays are u sed. They must be the same size, and the data contents are entirely up to the appli-

190



cation . The on ly integer codes not perm itted are 5 an d 105, as these are reserved for AutoCAD h an dles. New Xrecords w ill ha ve ha ndles added automatically when they are first created. All of the remaining group codes are at th e disposal of the a pplication . Tha t is, yo u can use group code 40 over and over again for a sequence of real numbers, or you can use 40, 41, an d so forth. Wha tever w orks best for th e application. To a dd a n Xrecord object to a dictiona ry, first open th e dictiona ry. We recom men d th at you dimension objects such a s dictiona ries in global memory so that they may be accessed by all modules in the application that need to get at them. With the dictionary already open, the next step is to a dd th e Xrecord object to it w ith th e AddXRecord() meth od. The AddXrecord() method creates an Xrecord object that is atta ched to the dictiona ry collection. The Xrecord data is then w ritten to the object using the SetXRecordData method. To learn ho w an Xrecord object is created, con sider the fo llow ing code. A new Xrecord object is attached t o a n a lready open dictiona ry object referenced by th e varia ble Dict as in th e previous code segmen t. The subrou tine w ill store a real num ber, an in teger, an d a string, w hich are all provided as param eters to t he fun ction. The key param eter is the key na me th at w ill be used for accessing th e Xrecord in th e dictiona ry a t some time in the future. Sub WriteXrecord(Key As String, R As Double, I As Integer, S As String) Dim Xtyp(2) As Integer Dim Xvar(2) As Variant Xtyp(0) = 40: Xvar(0) = R Xtyp(1) = 60: Xvar(1) = I Xtyp(2) = 1: Xvar(2) = S Dim Xrec As AcadXRecord Set Xrec = Dict.AddXRecord(Key) Xrec.SetXRecordData Xtyp, Xvar End Sub

191

A u t o c a d

V B A

PRO GR A M M I N G

The fun ction just provided m ight be used by an application th at h as th ree varia bles to be stored for fu tu re referencing. This function could be part of a mod ule tha t reacts to th e begin-save even t. The first step w ould be to o pen th e dictiona ry o bject by a ccessing a n existing on e or creating a new one. Next you’ll w rite the va riables. Tha t w ould end th e begin-save event reactor. You’ll need to do additiona l programm ing for the draw ingopen reactor, in w hich a module w ould open th e dictiona ry an d read the three va riables back into the a pplication .

Reading Xrecords To read a n Xrecord yo u m ust first know the key n am e it w as stored un der. The on ly alterna tive is to loop th rough th e dictiona ry on e item a t a time and examine the contents of each. In either case, the Item() meth od is used w ith th e dictiona ry object to o btain th e Xrecord object. From there, you’ll use the GetXRecordData() method to get at the group codes an d da ta stored inside. .GetXRecordDa ta() uses tw o varian t param eters. When the fun ction retu rns, these tw o w ill reference arrays of data . The first w ill cont ain a n a rray of integer values, an d the second w ill be an array of variants. The n ext code exa mple w ill read th e Xrecord created in the previous exam ple given th e key na me a s a param eter. The va lues read from t he Xrecord are put back into th e param eter variables to be used by th e calling program. You’ll use the fun ction GetXRecordDa ta() m ethod to return th e Xrecord con ten ts given th e Xrecord o bject reference. To o bta in the Xrecord object reference, the Item() method is applied against the already open dictiona ry o bject Dict. Sub ReadMyXrecord(Key As String, R As Double, I As Integer, S As String) Dim Xtyp, XVar As Variant Dim XR As AcadXRecord Set XR = Dict.Item(Key) XR.GetXRecordData Xtyp, XVar

192



For J = LBound(Xtyp) To UBound(Xtyp) If Xtyp(J) = 1 Then S = XVar(J) If Xtyp(J) = 40 Then R = XVar(J) If Xtyp(J) = 70 Then I = XVar(J) Next J End Sub

In this function, the values returned from the Xrecord read are processed int o va riables that are pa ssed back to th e calling fu nction . They could just as easily been placed into global variable locations for the application o r returned a s a result of a function in stead. The key item to remember is that Xrecords are under th e control of th e application program m an ipulating them . They can conta in an y ty pe of data desired and can be of any length. Note that they a re not protected from other programm ers w ho u nderstand how to na vigate the object system of AutoCAD, but th ey a re w ell protected from th e norm al AutoCAD user.

GroupCodesinXrecords Using grou p codes in a n application is entirely up to the a pplication s developer w ho is w orking w ith Xrecord objects. How ever, w e strongly recomm end tha t the follow ing standa rds be follow ed in order to remain consistent w ith th e AutoCAD draw ing data base. There are oth er group codes that can be used: these are m erely suggestions. Codes 1-9

What they are Strin gs. Do n ot use code 5, it is for th e en tity han dles!

10-29

Poin t lists.

30-39

Z va lu es. Som etim es th ese a re a lso used for poin t lists.

40-49

Rea l n u mber sca la r v alu es. Ty pica lly u sed fo r sizes a n d sca le fa ct ors.

50-59

Rea l n um ber a ngu la r va lues. Ty pica lly used for a ngles.

60-69

In teger n um bers, cou nters, a nd va lu es for n um eric co ded it em s.

70-79

In teger n um bers, bit cod ed fla gs com bin ed in to sin gle in tegers.

193

A u t o c a d

V B A

PRO GR A M M I N G

In th e previous exam ples, th e group codes used w ere 1 for the string, 40 for the real nu mber, an d 60 for the integer. They could just as w ell ha ve been 1, 2, 3 if the application creating th em w an ted the codes in that order. Xrecords ma y use group code num bers 1 throu gh 369. You sho uld no t use group codes 5 an d 105, as they are for AutoCAD. AutoCAD w ill add the entity handle information required to these codes. You may use all other codes in any manner you desire.

ExtendedData The other w ay to atta ch data that is non -graphical to a draw ing is to use extended data. You attach extended data directly to objects in the draw ing data base; these can con tain n um bers, points, and strings. The only rea l limit to keep in m ind w ith extended da ta is that a n object can have only about 16 kilobytes of data attached to it. Now, that is a lot of data to a ttach to an object in a draw ing, and it w ould not be good to attach even h alf that amo unt to objects throughout the da tabase. Operators w ill mo st definitely complain abo ut th e excessive disk space being consumed. When setting up extended data, you’ll use a group code system much like the Xrecord objects w ith th e exception tha t extended da ta grou p codes are a ll numbered in th e 1000 series. Extended da ta a ttachm ents mu st use these group codes w hen a ttaching da ta to a n entity o bject. Code

Extended data type

1000

Strin g.

1001

Application n am e.

1002

C on t ro l st rin g fo r n est ed in fo rm a tio n . Open bra cket fo r st a rt o f t h e nest, close bracket for the end o f it.

1003

La yer n a me. If t he la yer n a me is ch a nged in th e d ra w in g, t his field w ill be updated to reflect th at cha nge.

1004

B in a ry d at a st ora ge. Da ta ca n no t be accessed by Au to LISP a nd is u sed primarily by ObjectARX applications.

194



Code

Extended data type

1005

Ha nd le to an ot her o bject . C an be used to lin k o ne o bject to an ot her.

1010

Point.

1040

Real n um ber.

1041

D ist an ce — w ill be sca led w ith th e pa ren t o bject .

1042

Sca le fa cto r — w ill be sca led w ith th e pa ren t object.

1070

Integer.

1071

Lon g in teger.

Every object in the AutoCAD drawing database can have extended data attached to it. But before you can use extended data, the application ID m ust first be a dded t o th e registered a pplication s collection . The Add() method is used to add the name of your application to the registered applications collection object in th e current draw ing, as in the follow ing line of code. ThisDrawing.RegisteredApplications.Add “MyApplication”

An a pplication na me n eeds to be added only once to a do cument. Once it has been added, it w ill be saved w ith the draw ing and can be used w hen addressing exten ded da ta for objects. There are tw o m ethods associated w ith a ll AutoCAD objects tha t y ou use to a ccess the exten ded da ta. They are SetXdata () and GetXdata (). The SetXdata() meth od is used to add extended da ta to a n o bject. The registered application na me m ust be supplied w ith the da ta provided to SetXdata(). If there is already data attached to the entity w ith the sam e nam e, it is overw ritten. When w riting extended dat a to a n object, you m ust update a ll the informa tion associated w ith the application, or som e data ma y be lost. If the application n am e is not supplied or ha s not been registered w ith the draw ing, the extended data w ill not be attached to the object. The ex ten ded d at a itself is supplied in tw o va riables. The first va riable is an array of integer group codes. The second is a varian t array of w ha tever data is needed an d m atches the group codes. You m ust use the 195

A u t o c a d

V B A

PRO GR A M M I N G

group code with the data type that matches the data being supplied—otherw ise, the extended data w ill not be w ritten properly. When retrieving extended dat a from a n object, you ’ll use tw o varian t objects. These objects are a ctually a rrays conta ining th e extended data in th e same fa shion a s it w as supplied to th e object in th e first place. Applications can rely on the o rder of group codes being preserved in extended data , un less som e other a pplication chan ges them becau se it used the same name.

SimpleXdataExample The exa mple presented w ill create a line, th en a dd extended data indicating the date a nd time th e object w as added to th e draw ing. The AutoCAD system v aria ble “ CDATE” is used for th is purpose. “ CDATE” cont ain s the current date an d time stored as a real num ber that can be preserved w ith th e entity object. The fo rma t of th e “ CDATE” v aria ble is thus: th e yea r, mon th, an d day va lue are concatenated into the front part of the number (before the decimal point), and the time of day values make up the part that appears to the right of the decimal point. The first step is to crea te th e line object. You ’ll creat e a new line from (1,1) to (2,1). It is left on the current lay er an d no oth er changes are ma de to it. The line is then added t o th e mo del space of the current draw ing an d the line object variable saved in variable Lin. Dim Lin As AcadLine Dim PV1 As Variant, PV2 As Variant Dim PT1(0 To 2) As Double Dim PT2(0 To 2) As Double PT1(0) = 1#: PT1(1) = 1#: PT1(2) = 0# PT2(0) = 2#: PT2(1) = 1#: PT1(2) = 0# PV1 = PT1: PV2 = PT2 Set Lin = ThisDrawing.ModelSpace.AddLine(PV1, PV2)

196



As in Xrecords, extended da ta records use tw o arra ys tha t conta in the integer group codes and variant data values that comprise the extended da ta . The n ext section o f code initia lizes th e arra ys, retrieves the “ CDATE” value, adds the registered application name to the drawing, and places the extended data onto the line object. Dim PT(1) As Integer Dim PV(1) As Variant Dim RR As Double RR = ThisDrawing.GetVariable(“CDATE”) PT(0) = 1001: PV(0) = “CREATED” PT(1) = 1040: PV(1) = RR ThisDrawing.RegisteredApplications.Add “CREATED” Lin.SetXData PT, PV

The SetXData meth od is used w ith th e line object to a dd th e extended data array s to the object. G roup codes 1001 an d 1040 w ere used to h old the registered application na me a nd the rea l num ber value for t he CD ATE system variable. If you need to store an additional real number, the group code 1040 is used again. On e problem th at can come up is if mu ltiple applications are w orking w ith the same extend ed data . In tho se cases, it is possible that the o rder of th e dat a elemen ts could get con fused. We highly recommend that you keep extended data grouped in small data packs if there is a requirement for multiple applications to be messing w ith th e data . Each of t he da ta packs is assigned a registered application name, making it easy to retrieve just the data sought after. This next section of code w ill locate t he line w ith t he “ CREATED” extended da ta an d read th e saved v alue for th e CDATE system va riable. In order to a ccom plish th e task, the fun ction mu st first locate the line. This is don e w ith th e selection set ut ility fu nction . The fun ction G etda te() is presented in pieces, follow ed by a description of ea ch section of code. Function Getdate()As Double Dim SS As AcadSelectionSet

197

A u t o c a d

V B A

PRO GR A M M I N G

On Error Resume Next Set SS = ThisDrawing.SelectionSets.Add(“TEMP”) If Err.Number <> 0 Then Set SS = ThisDrawing.SelectionSets.Item(“TEMP”) Err.Clear SS.Clear End If ‘function continues

The fu nction starts by defin ing a selection set object na med S S. When you add the selection set “ TEMP” to t he selection set collection of th e draw ing, an error w ill result if it is already in th ere. Thu s w e enable the On Error trap to fo rce VBA to contin ue in th e event a n error is reported. If the error number is not 0, then VBA had a problem adding the name “ TEMP” to th e selection set collection . Tha t m ean s it is alrea dy in th ere, an d w e must use the Item() method to get it. Because selection set is already defined, it ma y a lso cont ain o bjects. The Clear m etho d is employed to clear out a ny objects tha t ma y be in th e selection set na med “ TEMP” . At this point in th e code, th e varia ble SS is a selection set object w ith not hing in it. The n ext step is to locate th e “ LINE” object tha t conta ins the extended data. ‘function continues Dim FT(1) As Integer, FV(1) As Variant Dim vFT As Variant, vFV As Variant FT(0) = 0: FV(0) = “LINE” FT(1) = 1001: FV(1) = “CREATED” vFT = FT: vFV = FV SS.Select acSelectionSetAll, , , vFT, vFV ‘function continues

You’ll use the Select method from the selection set object to locate the desired graphics. We are interested in finding a “LINE” object that has extended da ta atta ched to it. The techn ique inv olved in building th e filter 198



for extended data is som ew ha t different in VBA com pared w ith AutoLISP/Visua l LISP. In stead of searching for a –3 group cod e, it searches for th e 1001 group code. The 1001 group code con ta ins the a pplication na me tha t w ill be exactly the same a s the application na me supplied in the function th at w rote the extended da ta in th e first place. For our application , tha t na me is “ CREATED” . The filter list is built by first defining tw o a rray s. The first arra y con tains the integer group codes for the data w e are filtering. In this exam ple w e are looking for a “ LINE”, th us group code 0 is used. Entity na mes are alw ay s associated w ith group code 0. The extended da ta a pplication n am e is represented by the 1001 value. In the second array, the values for these va riables are set. The second a rray is of th e ty pe Varia nt so th at it ca n hold any type of data: string, double, or integer. Due to the way variants are stored in the VBA system, a second assignmen t is ma de for t he a rrays to an oth er pair of Varian t va riables (vFT, vFV). All the input elements for the selection set building function are ready, and Select() is called on to put entities into the selection set SS. The a cSelectSetAll is an AutoCAD consta nt th at tells th e select meth od t o search the entire draw ing databa se. At the end of this section of code, the selection set variable SS now contains a “LINE” object that has the extended data attached to it. If the “ LINE” object is not fou nd in the dra w ing, the selection set w ill be empty. ‘function continues If SS.Count > 0 Then Dim Ent As AcadLine Set Ent = SS.Item(0) Ent.GetXData “CREATED”, vFT, vFV GetSavedDate = vFV(1) Else GetSavedDate = 0# End If End Function

199

A u t o c a d

V B A

PRO GR A M M I N G

The selection set Cou nt property tells us how ma ny objects w ere found ma tching the filter description w e just used. If the Co un t is greater tha n 0, then the Select() meth od w as successful in locating objects tha t fit our criteria. We know tha t the object w e w an t is a line, so w e define an AcadLine object. If the extended data had been attached to any AutoCAD object, a better choice w ould h ave been t o use the AcadObject definition instead. How ever, due to th e w ay VBA w orks w ith libraries an d objects, the general rule of thumb is that w hen yo u know exactly w hat object type w ill be encoun tered, use that definition. This w ill improve the performa nce of the applications. The a pplication looks at th e first line in th e selection set w ith Item (0). The G etXData() meth od is used w ith th at en tity object to retrieve tw o variants that are arrays containing the extended data. Since our application w rote only tw o pieces of data to th e extended data, the n ame of the application an d the data value, w e know for certain tha t the value saved can be foun d in th e second elem ent. Thu s, use vFV(1) to get the sav ed value and place it in the return value of the function. If the selection set coun t w as 0, the function didn’t find a ny “ LINE” objects w ith the exten ded data atta ched. At this point, th e return result of the fu nction is set to 0. A more advanced look at extended data manipulations is presented in a later chapter. Extended data provides a pow erful w ay to associate nongraph ical data t o existing graphical objects. There are ot her techn iqu es tha t can be used as w ell, including Xrecords w ith ha rd ow nership relationships. How ever, these are mo re difficult to m an age un der VBA, an d mo st a pplication s w ill find ext ended da ta t o suffice.

200

C h a p t er

T w e l v e

Gotcha’s and Tips

E

very programming language has its quirks and special rules. VBA is no exception. Some o f the w ay s it w orks contrasts w ith other langua ges you ma y a lready know ; this cha pter discusses

some of th e interesting featu res yo u w ill encoun ter. Subjects covered in this chapter are: • Handles data types comparison • Uses control keys to maneuver • Creates User defined Objects • Ha n dles d a tes • Handles Arguments • P o in t er Ar gu m e nt s • Short Circuit Evaluation • Access objects on th e sam e level • Creates colum ns and uses multiselect in th e listbox control • Deals w ith Variable nam e shortcuts • Deals with the Command line in AutoCAD

288

C H A P T E R TW E L V E : G otch a’s an d Tips

If you don ’t know w hat some of th ese things are, do not despair. Some of these are advanced topics and we will endeavor to explain them as we get into the va rious nua nces of the VBA program ming environm ent.

Divergent DataTypeComparison VBA variables can be declared w ithout assigning a d ata type. Tha t mea ns it is possible to compare va riables that are n ot o f th e same ba sic data type. VBA automatically assigns a default data type of Variant w hen o ne it not declared. It is a good idea to get into th e habit of assigning the da ta type w hen ever you plan to use a v ariable. This is helpful from a m ainten an ce point of view an d for checking divergent data types. Before discussing a techniqu e for avo iding problems w ith divergent da ta t ypes com parison, a discussion of h ow VBA han dles data ty pes is in order. The da ta t ype of y ou r varia ble sho uld be determ ined by th e type of data you w ish to store. Table 12.1 show s the va rious data types you can declare in VBA.

Table12.1: VBADataTypes Data type

Value it can hold

Comments

B yte

0 to 255

Sm a ll positive in tegers.

B oolean

-1 or 0

Yes or No va lues.

Currency

-922,337,203,685,477,648 to 922, 337, 203, 685, 477, 647

Use for money calculations w hen accuracy is im po rt a n t. M a xim u m of f if te en dig it s t o t h e left of the decimal and up to four digits to the right.

Da te

J an . 1, 100 to Dec. 31, 9999

Data a nd tim e.

Decim al

Varian t subty pe

Declare your va riable as a varian t and then use the CDEC() function to convert th e variable to a decimal data type. Beha ves like the currency da ta type but is mo re precise.

Double

Approx. 4.94E-45 to 1.8E308

64-bit double precision floating point.

Integer

-32768 to 32676

Sm a ll positive an d n egative n um bers.

Lo ng

-2,147, 483,648 t o 2,147,483,647

La rge w h o le n um ber, bo th po sit iv e a n d n egative.

289

A u t o c a d

V B A

PRO GR A M M I N G

Data type

Value it can hold

Comments

Sin gle

Appro x. 1.4E-45 to 3.4E48

32-bit sin gle precisio n floa tin g po in t

Strin g

0 to 2 billion ch ara cters

Text

Va ria n t

C a n co n ta in a n y t ype o f d a ta in clu din g Em pt y o r Nu ll

Th e d efa u lt d a ta t y pe w h e n y o u d o n o t decla re o ne. Ca n ho ld da ta of a ny ty pe.

Most of the data types are self-explanatory, except the Variant type. Variant is very special in VBA. It can hold any of the other data types so yo u can use it as a general catch-all. There are some th ings to w atch o ut for with the Variant, however. Since it can contain any type, you might be tempted to use it almost to the ex clusion of t he o thers. This w ill cause problems such as divergent data type comparison. Other disadvantages of using it exclusively are that it takes more storage space, and maintaining the code is mo re difficult because it is no t obvious w ha t ty pe of data you are holding. Despite these disadvantages, there are many good uses of Variant variables. One is for passing pointers to a rray s as argum ents. Since VBA does not allow you to pass an array into a subroutine or function, you pass in a pointer to the array, an d the subroutine or function can then a ccess the a rray th rough th e pointer. Anoth er advan tage of a Variant v ariable is its ability to conta in a NULL or Empty va lue. This is very useful w hen dealing w ith da taba se records since the Microsoft J et engine returns all data in variant form. Switching it to other data types and back would be a waste of time. Another good use is when you’re working with listbox values. A listbox value is a variant. You need to declare a variant type to take its value and use it. If the user has not selected anything in the listbox, you could get a NULL value. You should check that you have a value before proceeding. When y ou a re not sure w hat type of data you r variant might contain and you need to find out before processing it; you can use a special VBA function to determine th e data type of th e data stored. The fun ction is called VarType. Pa ss yo ur Varian t va riable to th e fun ction a nd it w ill return the data type of the data in the variable. 290


Tw o special valu es (Null an d Em pty) a re used w ith Varian t va riables. To test fo r th ose special va lues yo u m ust u se the IsNull or IsEmpty tests. Once you get a response from th e test, you can th en do further tests or process the data. One adva nta ge of the Null value is how it beha ves w ith VBA operators. When you use an operator like plus (+ ) w ith a NULL value variable and a string or num ber, yo u get NULL as a result. Bu t w hen you use the am persan d (&) operator w ith a NULL value v ariable an d a string or nu mber you get the string or num ber back. This is w ha t allow s you to get aroun d the problem of divergent data types that could contain NULL values. An exam ple is show n below . Dim groupname As Variant groupname = frmGroupNames.1stGroupNames.Value

If Len(groupname & "")>0 Then

‘#1 - this works

In the exa mple, the Variant v ariable group na me is loaded w ith the conten ts of th e listbox (lstG roupNam es) selection. There is no gu ara ntee tha t an yth ing is selected or tha t a va lue w as passed. If you w ere to check for the length of th e variable and it contained NULL, the program w ould stop w ith a n error. The Len fun ction m ust ha ve a string passed to it. Line #1 is th e w orkarou nd t o th e problem. VBA binds the zero-length string w ith th e Varian t va riable. When the v ariable is NULL, it return s a zerolength string tha t the Len fun ction can deal w ith. When th e variable ha s someth ing in it, a zero-length string is added, but th at does not a ffect th e original string. This sam e meth od can be used w ith da ta base recordsets. You can also use it w ith nu mbers instead o f strings. Just remem ber that you mu st use the a mpersan d—not a plus sign.

Using Control Keys to Maneuver in VBA The standa rd Microsoft fun ction keys apply w hen you ’re cutting a nd pasting inside the IDE and function key {F1} gets you help just like in

291

A u t o c a d

V B A

PRO GR A M M I N G

an y Microsoft produ ct. There a re a few special VBA con trol keys sequen ces that can really m ake you r life easier w hen programm ing. Finding code in your project is one of the most difficult things about object-oriented code. Other common useful tools are the ability to shift (indent) blocks of code and the a bility to step through y our code on e line a t a time in debug m ode. VBA ha s given y ou some shortcuts for accom plishin g these thin gs. These cont rol keys seq uen ces are • Ta b • S hif t + Ta b • {F2} • Sh ift + {F2} • C trl + S hift + {F2} • {F8} We indent our code for maintenance purposes. It is easier to read the code w hen it is lined u p vertically. To in den t code blo cks yo u u se the Tab or shift + Tab key sequ ence. The pro cess is sim ple. Ju st high light th e lines of code, th en use the key sequ ence. The Tab key m oves th e code block to th e right w hile the Shift + Tab key sequen ce moves the code block to the left. The {F2} key ta kes yo u t o th e Object brow ser covered in Ch apter 3. One of th e most useful key seq uen ces is the Sh ift + {F2} key sequen ce. You use this to bounce around in the module definition, variable declaration , or object brow ser. You place yo ur cursor over a va riable or procedure an d use the key sequence. VBA w ill take you to th e declaration of the variable or to the declaration of the procedure. When you place your cursor over an object, the key sequ ence takes you to th at object’s definition in th e Object B row ser. The Ctrl + Shift + {F2} key sequen ce returns you to th e last position of yo ur cursor (unless yo u a re in th e Object Brow ser). These features are very ha ndy for moving q uickly a round th e code modules, checking tha t

292


yo u ha ve properly declared a va riable w ithin th e current scope, or seeing an object’s definition an d getting back to w here you started. When debugging you r code, the {F8} function key is of prima ry im portan ce to you. It allow s you to step into (through) yo ur code one line at a time. You can place a break point on a line of code near the suspect code, and th e program w ill halt processes at th at line in a w ait state. You can th en use the {F8} key to m ove one line at a time th rough th e problem area, viewing variables or watching the flow of your program. There are ot her key sequ ences tha t w ork w ith {F8}. You ca n see them o n the Debug pull-down menu.

ClassModulesin VBA VBA has the a bility to define a class similar to lan guages like C+ + or Delphi. Classes allow yo u to define y our ow n objects. They are th e definition of the object. In AutoCAD terms, you could compare a class to a block table definition and an object to the block occurrence in the draw ing. One class definition w ith m ultiple objects derived from it. You define properties and methods for your objects in the class module. You can th en use the class definition over an d over aga in in the form of objects w ithin the program. Since one program can reference an other program ’s code, you can sha re the object w ith oth er program s. The difference betw een a VBA object defined in a class mo dule an d on e defined in C+ + or Delphi is tha t th e VBA object cann ot inherent like in the o ther lan guages. Inh erence is an import feature of h igher level object-oriented lan gua ges. It allow s yo u to define a paren t object and spaw n o ther o bjects (copies) from the pa rent. The o ther o bjects can ha ve additional methods or properties added to the original parent object’s metho ds and properties that a re uniqu e to th e child. When a parent object’s method or property is altered, all children objects’ methods and properties chan ge in accorda nce. In VBA, you a re allow ed to create instan ces of the origina l parent object (mu ch like in C+ + ), but th e child object does not stay linked to th e parent. This mean s tha t chan ges ma de

293

A u t o c a d

V B A

PRO GR A M M I N G

to the parent object are not reflected automatically in the child objects derived from the parent . This is w hy VBA is not a true o bject-oriented programm ing language; a lthough it ma y seem like a sma ll difference, it can be importan t w hen dealing w ith large families of objects. Still, you can create you r ow n o bject w ith VBA. You can create a person object tha t ha s properties like age, height, w eight, race, religion , an d an y other informa tion y ou n eed to access about a person in a class modu le. Tha t object is then av ailable to hold inform ation in yo ur program . You need only declare the object in y our procedure, then loa d the informa tion into it. Objects can be very h an dy w hen w orking w ith data base recordsets. Other uses for yo ur ow n created objects include but a re not limited to: • Wrapping API procedures • Wrapping Registry calls • Handling reading and w riting with ASCII files • Creating new properties for forms • Defining real w orld compon ents of your designs such as structural mem bers or survey da ta • Holding stan dard calculations used to solve engineering problems • Implementing individual client or governmen t styles an d stan dards A form is a special class modu le VBA provided for us. It con tain s collections of controls that w e can use. When you create a form, y ou a re filling in details about the form a nd a dding your ow n m ethods (subroutines) an d properties. To show how this all w orks, w e ha ve provided a sma ll exam ple project. Open up the Employee.dvb provided on the sample CD and call the TestEmp m odu le from y our imm ediate w indow . The m odu le has a stop in it to allow you to step though the code one line at a time using the {F8} key. You can see the values assigned to the properties of the employee by placing your cursor over the property as you press the {F8} key. This meth od o f stepping th rough the code is also u seful because yo u see how the program m oves through the class modu le code. 294


The mo dule has hard lined an age and n am e into a new ly created object called employee. It does nothing more than put up a message box w ith the employee’s na me an d age in it to show tha t the object took the data . Look at the Employee class modu le to see the property an d m ethod definitions relating to the employee o bject. The d eclara tions in th e class module are the properties, and the procedures in the class module are the m ethods. VBA even recognizes them an d uses them in the modu les w ith the Intellisense feature. After you create a n ew instan ce of the employee object in a module and set it, you can see the properties and meth ods by ty ping in th e object’s nam e an d a period (.). The In tellisense featu re of VBA sho w s th e properties an d meth ods defined in th e class modu le for employee.

TheSpecial Character for DateandFormat The Da te an d Form at fun ctions return a Varian t data ty pe. Because of this, it is important to either use a string or to w rap a da te string you w ish to convert to a da te w ith the special poun d symbol (#). Otherw ise, VBA interprets the separa tor sym bols as mat hem atical operat ors. The exam ple below illustrates the differences. Dim MyDate, ADate, BDate, MyStr As Variant Adate - 1/27/93 MyStr = Format(ADate, "Long Date")

'Line #1

BDate = "1/27/93" MyStr = Format(BDate, "Long Date")

'Line #2

MyDate = #1/27/93# MyStr = Format(MyDate, "Long Date")

'Line #3

Line #1 returns MyStr as the incorrect date of Saturday, December 30, 1899. This is becau se the VBA is dividing th e nu m bers in ADa te (gett ing 3.982477E-04 ), and then the Format function is converting the number to a d ate. Line #2 returns MySt r as the correct dat e w ith a va lue of Wednesday, J an ua ry 27, 1993. This is because BDa te conta ins a string value,

295

A u t o c a d

V B A

PRO GR A M M I N G

and the Format function will handle a Variant data type containing a string da te. Line #3 also return s My Str as the correct dat e w ith a va lue of Wednesday, J an ua ry 27, 1993. This is becau se MyD ate uses the special date chara cters aroun d it, an d the Format function ha ndles a Variant data type containing special date characters. Collecting a da te from a user on a form an d converting it can cause unpredictable results unless you know the da ta ty pe of the control you got the data from. A text box w ill return a string tha t w orks w ith the Forma t function, but a listbox value returns a variant, a nd it might n ot be in string format. It is safest to use the special date characters around data tha t you collect and w ant to convert into the date format w ith the Format function. You can see the example by opening the Dates.dvb project supplied on the CD and stepping through it by calling the PlayDate subroutine in the Immediate window.

NamedandOptional Arguments First a brief definition of terms. An argument is the data being passed into a procedure from outside the procedure. A parameter is the data accepted inside the procedure from the calling procedure. Most programmers use the terms intercha ngeably, but they really apply to th e point of view you are taking w hen discussing the data passing. Here w e are discussing passing in the data , so w e are ta lking about argumen ts. VBA allow s yo u to pass argum ents into a procedure (subroutine or function) by th ree methods. You can supply the a rguments in th e order they are declared in th e procedure, by n am e (in no particular order), or by a declared optional argument. Given a sample subroutine called ShowEmp you can see how to call using th e various methods. You can run the sample routine by o pening the Arguments.dvb project supplied on the CD and copying the samples (supplied as com men ts in th e routine) into th e imm ediate w indow . The procedure declaration is show n below an d the declaration is w ha t is needed to explain the concept.

296


Public Sub ShowEmp(strName As String, intAge As Integer, dteBirth_ As Date, , optional height as integer)

Passi n g in th e Declar ed Or der The defa ult w ay to call the sub-

routine w ould be to call it and pass the argum ent in the order you defined th em. The call for th is meth od m ight look like this. Call ShowEmp (“My Name”, 22, #2/3/76#)

Or ShowEmp “My Name”, 22, #2/3/76#

The n am e is a string in th e first position, age is an in teger in the second position, and the birth date is in the third position. Passi n g by N ame You can pass the a rgument s into the procedure by

nam e in an y order you w ish. The method is to give the nam e of the argument in the procedure’s declaration follow ed by a colon, equa l sign, a nd the va lue. For the same procedure call to Show Emp listed abo ve, the call w ould look like this: Call ShowEmp (bDate:=#2/3/76#, Name:=”My Name”, Age:=22)

Or ShowEmp Age:=22, bdate:=#2/3/76#, Name:=”My Name”

The position of the a rgument does not m atter w hen y ou u se the nam ed argument method. Optional Ar gum ent s The sam ple routine supplied for th e tw o previ-

ous argument m ethods show s how the third optional argument m ethod w orks. Each of the exam ples show n w ill w ork fine even thou gh the height a rgum ent w as not present. Tha t is because the Option al declara tion was used for the height argument. When you want to pass the height into the routine, just add it to the list of arguments. There is only on e rule for o ptiona l argum ents. They mu st be declared last in th e procedure’s declara tions. You can put in as ma ny as you w ish as long as they are last in the list. When you use the optional height argumen t, y our call might a ppear like this:

297

A u t o c a d

V B A

PRO GR A M M I N G

Call ShowEmp (“My Name”, 22, #2/3/76#, 72)

Or ShowEmp Age:=22, bdate:=#2/3/76#, Height:=72, Name:=”My Name”

Notice tha t even th ough the declaration ha d to be last in the procedure, the call does not h ave to m ake it last w hen using nam ed arguments.

Using anArrayof Doubles versusa Variant asan Array VBA for AutoCAD does not allow you to pass an array to a procedure. To q uo te Autod esk from its 14.01 Release FAQ statem ent , “ Suppo rt for early binding requires tha t a ll properties of, and argumen ts to, meth ods w hich pa ssed da ta as an arra y m ust n ow be passed as a VARIANT. This includes all 2D and 3D coordina tes, w hich ha ve previously been passed as arrays.” The tru th is tha t w hile VBA can pa ss arra ys as argum ents to procedures w ithou t causing an error, procedures can no t declare arra ys in th eir declaration list. This can som etimes be a bit con fusing because you can define a n a rray in th e calling procedure and pass it into an other procedure—but only if you define the incoming parameter as a Variant. We ha ve illustrated below the w ay to pass an array t o a procedure. Dim Pnt(0 To 2) As Double Dim Ent As Object Call GetEnt(Ent, Pnt)

Public Sub GetEnt(Ent As Object, Pnt As Variant)

High-level languages such as C use pointers to pass around variables an d array s. The on e trap tha t yo u can fall into in VBA is thinking that you can define the array in the calling procedure and set a va rian t pointer to it, then pass the pointer to the called procedure (much like

298


in C). This meth od simply does not w ork in VBA. Since there is no pointer capability in VBA, the passed-in variant is treated as the array, an d th e original a rray is never filled. This is because the v arian t is passed-in by reference as its ow n variable and n ot a s a pointer to th e arra y it w as set eq ua l to. This w ho le process exists because you ca nn ot pass arra ys into procedures imposed by VBA on t he procedure’s declaration list. B elow w e ha ve illustrated a typical situation of passing a Varian t into a procedure. The called procedure can then use varian t as an arra y an d fill it u sing subscripts. The filled array is then ava ilable in the calling routine after the called procedure is finished. Dim Pnt As Variant Dim Ent As Object Call GetEnt(Ent, Pnt) Public Sub GetEnt(Ent As Object, Pnt As Variant)

The Pn t va riable con tain s an arra y point created by the selection object. You can access each of the elements in the Pnt variable as though it w as a declared array. The sam e problem exists in t rying t o pass the arra y ba ck to the ca lling procedure. An array canno t be used to receive data from a procedure. Alw ay s use a varian t in both th e calling procedure an d the called procedure in tha t situation . The project called Array.dvb supplied on the CD w ith this book has three procedures you can experiment w ith to see th ese differences. They are • Pass_Declared_Array • Undeclared_Array • Pass_Undeclared_Array To check them o ut, y ou m ust load th e project and call each on e from the imm ediate w indow in the Visual B asic Editor.

299

A u t o c a d

V B A

PRO GR A M M I N G

Passing an Arrayto an Object’s Methods Passing a n a rray to a n object’s meth od w orks great, but it is different from passing one to a procedure. Some of the Autodesk object methods want an array of doubles passed to them for holding data (usually a point). Several of the mo del’s utility object methods w an t an array of dou bles for poin ts. They includ e: An glefromAxis

G etAngle

G etCorner

G etDistan ce

G etEn tity

G etOrientation

PolarPoin t

Tran slateCoordin ates

Another AutoCAD model object that uses the array of doubles as an argum ent fo r its meth od is the Selectionset object. The m etho ds are • SelectAtPoint • S ele ct B y P oly g on • SelectOnScreen There are m an y m ore exam ples in th e Auto CAD object model. In gen eral, w hen a model object’s metho d needs a point, it needs an array of doubles. We have show n th e syn tax for that ty pe of operation below. Dim ptPick(0 To 2) As Double ThisDrawing.Utility.GetEntity mobjBlockRef,ptPick,"Select a Block"

CollectionsandProcedures Collections are similar to arra ys but w ithou t all of the storage restrictions. You do not have to declare the size of a collection before using it, and you do not have to change the declaration of the collection as you add mo re items. The on ly restriction placed on y ou w ith collections is tha t y ou can not store user-defined da ta ty pes in them . Dim colOne As New Collection colOne.Add 1 colOne.Add 2

300


colOne.Add 3 Dim i As Long For i = 1 To colOne.Count Debug.Print "colOne(" & i; ") = " & colOne.Item(i) Next i

The a bove code illustrates how to spin th rough a collection to retrieve items. Collections are indexed from 1, not 0, as in an array. You have a Coun t property to find out h ow ma ny items are in th e collection. The Item metho d allow s you to retrieve data from a collection w hile the Add and Remove methods allow you to put data into and delete data from a collection. Below w e ha ve illustrated creating a collection, a dding items to it, spinning throu gh it, an d printing th e items. Dim colOne as Collection Set colOne = New Collection

Passing a collection to another procedure to be loaded is similar to passing an array, except that you do no t need to use a Variant containing the collection. You can pass the collection directly, a nd th e procedure w ill load it. One feat ure of the collection object is tha t yo u can d eclare it in one line an d initiate it in a noth er. The exam ple show n a bove illustrates the one-line method of declaring and initiating the collection, called colOne, at the same time. Another metho d is show n below. Dim colOne as Collection Set colOne = New Collection

This ability t o split the declaration an d initiation of a collection a llow s you to pass in a declared and initiated collection or just a declared collection a nd let the called procedure load the collection. Unfortun ately, y ou cann ot pass back a collection, so you m ust alw ay s use an existing collection tha t is passed in, similar to th e arra y problem. This is a min or problem an d just a noth er trait of the lan guage. Several collection exam ples are provided on the CD in the Collection.dvb project. Run an y of th e follow ing procedures to see ho w collections beha ve. 301

A u t o c a d

V B A

PRO GR A M M I N G

• Pass_Collection • Pass_Declared_Collection • Pass_Undeclared_Collection • Return_Collection You can load the project and run a ny of the procedures in th e immediate w indow . The Retu rn_Collection procedure w ill fail. This is done to illustrate that Collections cannot be passed back from a function.

Short Circuit Evaluation Short circuit evaluation is the order the lan guage deals w ith in a complex condition statement. In AutoLISP, the language checks the first part of the condition, then m oves on to the next part of th e complex statemen t w hen the first condition is valid. This is very conven ient: it lets you check to see if a variable has a value bound to it before you compare it w ith something in the sam e line of code. In VBA, you can either syn c the da ta ty pe for comparison o r do everything a ll on one line of code— or y ou can first check to see if the v ariable exists. Then place the code for the comparison inside the true condition of the statement. Earlier in the chapter, w e gave you an exam ple of syn cing the divergence data type for comparison. Tha t exam ple allow ed you to check on on e line of code a va riable that m ight conta in a NULL. The exa mple sho w n below illustrates the longer w ay of testing first, then cont inuing to process. Dim groupname As Variant groupname = frmGroupNames.1stGroupNames.Value If Not IsNULL groupname Or Len(groupname) > 0 Then

' #1 -

'causes an error If Not IsNULL groupname

' #2 - first

'see if its null or not If Len(groupname) > 0 Then

' #2a - now

'check if it has data in it

302


The line #1 cau ses an error in VBA w hen the v ariable conta ins a NULL because it is an exam ple of short circuit eva luation (w hich does no t w ork in VBA). VBA does not stop evalua ting th e line of code just because the last thing it evalua ted w as invalid. In Auto LISP (different syn tax, sam e principle), it w ould w ork because the language w ould stop evaluating the line of code w hen th e groupname w as NULL. Lines 2 and 2a are on e w ay of doing it in VBA. VBA w ould never get to th e 2a line of code because it w ould evaluate th e line 2 code and mo ve past the true condition w hen the va riable con tain ed NULL. The more elegant w ay o f testing w as show n in the divergence data ty pe comparison section, ea rlier in t his chapter. The bottom line is tha t w hen y ou get an error on you r condition statement of invalid data type, chances are that you have run into short circuit evaluation. You can solve the problem by the above example or by syncing the data type as show n in th e divergence data ty pe comparison section of th is cha pter.

AccessingObjectsontheSameLevel as Your Current Object The Auto CAD object mod el ha s a hiera rchy. The Auto CAD application is the top object, w ith everyth ing in it un der it. When y ou a re w orking w ith an object and you n eed to w ork w ith a nother object on th e same level of the hierarchy, you need to specify the parent object first. Under the application object are the preference and document objects. While w orking w ith the docum ent object, you can a ccess th e Preferences object by explicitly u sing t he a pplicat ion o bject in th e call to th e object. Figure 12.1 illustrates part of the object model’s hierarchy. The Applicatio n o bject represents the a pplicat ion ’s fram e cont rols an d path settings and provides the m eans to n avigate dow n th e object hierarchy. This can be con fusing because the a pplication a ppears to be un der the special object called ThisDraw ing, but it is actua lly th e oth er w ay aro un d. ThisDraw ing is the special shortcut na me for the docum ent.

303

A u t o c a d

V B A

PRO GR A M M I N G

Figure 12.1: AutoCAD Object Model

Given th e follow ing code, you could access the preferences w hile w orking on the docum ent (same level). Dim preferences As AcadPreferences Set preferences = ThisDrawing.Application.Preferences

Dim strConfigFile As String strConfigFile = preferences.ConfigFile

'line #1

The code in the exam ple sets the AcadPreferences object to the Preferences object by starting from th e documen t (ThisDraw ing), and m oving up the h ierarchy to the a pplication object, then dow n to the Preferences object, w hich is at t he same level as the do cumen t object. It can th en access any object un der th e Preferences object as show n in line #1. You can see how the object path is going up and then back down by reviewing the o bject model. Using th e explicit definition o f the w ho le object trail or using th e With statem ent a llow s yo u to a ccess an y object in th e object mo del at an y time. 304


ListboxColumns Listbox columns are one o f those things that y ou w ould think obvious, but even intermediate level programmers can stumble on it the first time. It’s not hard, but columns in lists are very flexible, and you can do ma ny th ings w ith them. For example: • You can ha ve up to ten columns in a listbox. • Columns can be different sizes. • C o lu m n s ca n b e h id de n . • You can specify w hich column w ill return a value on selection. • You can load data into the columns or specify a tw o-dimensional array that is linked to the listbox and its columns. • You can create a column header w ithin the listbox.

Multiple columns Creating the columns in a listbox is easy. Simply enter how ma ny column s you w an t into the Colum nCou nt property o f the listbox object.

Column Size Controlling th e w idth a nd visibility of th e colum ns is not as obvious, even thou gh th ere is a Column Width property on the listbox. The ColumnWidth property uses points per inch (much like a font) to measure the w idth. When y ou set a colum n position to 0, the column is not show n. If you do no t specify a w idth, the default is to split up the column s int o eq ua l sizes. The m inimu m size is 72 point s, but yo u can ma ke them smaller. A sam ple of column w idths for a listbox w ith three column s is show n below. ColumnWidth

| 40 pt; 40 pt; 40

305

A u t o c a d

V B A

PRO GR A M M I N G

It shou ld be noted th at you could use inches instead of points in the above example. Consult your VBA online help for ColumnWidths to see a mo re detailed discussion o f th is property.

Column Visibility To m ake th e second colum n in visible the C olum nWidths property w ou ld be set as show n below.

Listbox Column Specification One of the nice features of using columns in a listbox is the ability to access any of the columns on a selected item. You can set the BoundColumn property of the listbox to the column number you want values from w hen an item is selected in the listbox. This w orks even w hen the Bou ndColum n is set to a colum n th at th e user can’t see! This allow s you to build a tw o-dimensional array in w hich one element of the array is visible to the user but the other isn’t. When the user selects from their visible list, y ou can use th e invisible list to process the da ta item . This is a great feature for things like associating a color in English that the user sees, but processing t he a ssociated color n um ber behind the scenes.

Loading Listbox Columns There a re three m etho ds for loading listbox column s. Actually, only on e exam ple is a m ethod; the ot her tw o a re properties of the listbox. They are • AddItem • List • Colum n

AddItem Method The AddItem m ethod a llow s you to a dd data to a single colum n or m ultiple colum ns in a listbox. G iven a listbox called lstItemWindow w ith a ColumnCount of 1, the code to load an array of data into the listbox w ould look like that show n below . 306


Dim intI As Integer With IsItemWindow For intI = 0 To UBound(SampleArray) .AddItem SampleArray(intI, 0) Next intI End With

To a dd da ta to m ultiple column s (after chan ging the Column Coun t to 2) using the AddItem metho d, the code w ould look like that show n below. Dim intI As Integer With IsItemWindow For intI = 0 To UBound(SampleArray) .AddItem SampleArray(intI, 0) Column(1,.ListCount - 1) = SampleArray(intI, 1) Next intI End With

Notice that th e only difference betw een the single loa ding exam ple an d the on e for tw o column s is the line of code ma rked in italic. You mu st first use the AddItem meth od to put the da ta into the first column, then immediately use the Column property to add the next piece of data.

List and Column Properties Both of the a bove AddItem exam ples use a tw o-dimensional array (previously constructed a nd loaded). There a re tw o different properties of the listbox that allow you to link a two-dimensional array directly to a listbox w ithou t ha ving to loa d each item individua lly. The List property is just on e line of code as sho w n below . 1stListWindow.List = SampleArray

'load using list property

Ju st setting th e listbox property equa l to th e array is enough to a ccess all of the data in the array. The Column property behaves the sam e w ay 307

A u t o c a d

V B A

PRO GR A M M I N G

except for the fact that it inverts the X,Y of the array to Y,X. When you ha ve need of th is feature, you shou ld use the column property instead o f the List property. The loa d w ould look like tha t show n below . 1stColumnWindow.Column = SampleArray

'load using Column

property

Column Headers Column headers are available w hen y ou set the ColumnHeads property of the listbox to True. But th ey w ill w ork only w ith a row sources att ach ed. This is no t a fea ture th at can be current ly used w ith AutoCAD. To review the va rious column features, y ou can open the Column s.dvb project (supplied w ith this book) and run the Colum nSam ples ma cro.

Multiselect in a Listbox There a re three t ypes of selection y ou can use in a listbox: • fmMultiSelectSingle to select a single item at a time. • fmMultiSelectSingle to select mu ltiple items one at a time. • fmMultiSelectSingle to select multiple items in groups using the shift key. Single item selection is the default, but you can select any of the three using th e Mu ltiSelect property o f th e listbo x. The supplied sam ple project Colum ns.dvb h as three list boxes on the fo rm. The left on e is a single select box, the middle one is a multiselect box, and the right one is an extend ed m ultiselect box . To review the variou s multiselect fea tures, yo u can o pen the Co lumns.dvb project (supplied w ith th is book) and run the Column Sam ples macro. After loading some da ta into th e boxes, try selecting in each box to see the difference in how they w ork.

308


VariableNameShortcuts VBA has the ability to recognize the old Basic shortcut variable naming conven tions (type-declarat ion cha racters). This is very ad van ta geous for those programmers who already know the shortcuts but very confusing for those who don’t want to dump a portion of their childhood from mem ory just to mem orize them. The shortcuts are cryptic an d ha rd to understand un less you kno w them or ha ve a chart. An exam ple of the ty pe-declara tion cha ra cter in an Int eger is th e percent sign (%). This is counterproductive during programm ing or m aintena nce. It is better to spend your time working on the design of the program than trying to understand a cryptic variable data type. It is best not to use the old shortcuts for these reasons. Programmers maintain code much more than they create new code (just like editing in Auto CAD is don e more th an creating n ew geometry). Why add extra effort to deciphering the code?

Command Line Workaround This section is provided becau se VB A in Aut oCAD Relea se 14 w as a you nger implementa tion in AutoCAD, a nd not all objects w ere exposed yet. Because of this, you actually need a command line workaround for dealing w ith objects that a re not in th e VBA object model yet. Autodesk said it couldn’t be done, so n atura lly w e ha d to find o ut how to do it. (We must give credit w here it is due to Andy B aron an d Mike Gun derloy of Application Developers. They researched a nd w rote the code that makes it possible to use the command line from a VBA application .) We also need to stress that y ou sho uld no t use this w orkaro un d for an yt hing ot her th an no n-supported objects in VBA. The exam ple provided is for ext erna l references. VBA for Auto CAD Release 14 does not ha ve an Xref object, thereby limiting it from w orking w ith externa l references. It is on e of the few legitima te reasons for havin g access to the command line from VBA at this time, and it is the main reason this section is included in th is book.

309

A u t o c a d

V B A

PRO GR A M M I N G

The Problem The linka ge code in VBA beha ves much like a DDE linkage in VB on the surface. There is no w ay to mo nitor w ha t’s ha ppening on th e comma nd line. You a re stuck w ith jam ming a comma nd dow n th e pipeline and ho ping for the best. To furth er com plicate the m att er, you ca nn ot w ork arou nd th e problem like yo u can in VB becau se w hile VBA is run ning, the comm an d you sent dow n th e pipeline is not executed. It happens only w hen VBA is done. In VB w ith DDE, a common method w as to stay in a loop until a semaphore w as set. You w ould launch th e code to AutoCAD, then sw itch to a w ait state subrou tine looking for the sema phore. The laun ched code w ould set the sema phore as the last thing it did and y our w ait state code in VB w ould see the chan ge and allow the program to continue. This w orked because VB w as in its ow n m emory pool, not AutoCAD’s, an d it did not have to close dow n to run the comma nd. Because the command will not run until the VBA macro ends, you cannot put VBA into a wait state looking for a sema phore. It w ould just h an g there since the semaphore w ould never get set because the launched code w ould not run un til VBA stopped. A perfect cat ch-22. Fortuna tely, there is a solution .

The Solution The supplied project called Com ma nds.dvb h as a sendcom ma nd m acro definition tha t illustrates how you can use the w orkaround. The w orkaround in VBA is to send one string to AutoCAD to be processed and tack on the AutoLISP (command “-vbarun” “sendcommand”) line at the end of w ha tever code you ’re sending do w n th e pipeline. The result is tha t VBA sends the comm an d an d dies. Then the com ma nd line is executed in AutoCAD, and the last line of the stream is the call to load the ‘sendcomma nd’ m acro again. To u se this in production w ould m ean tha t y ou w ould n eed to save the va riable state in VBA before closing dow n a nd restore it again w hen starting up (a simple enough procedure using the registry or a temporary file).

310


This solution is no t pretty a nd needs testing to determine if it blow s the comm an d stack or causes any other m emory problems—but it does work.

Passing Forms and Controls as Parameters A sma ll problem a rises w hen you w an t to a ccess the information o f a form or control w hen not in the form’s module. You can ’t directly do it. You must pass the form or control into the procedure to access its properties and methods from any other module than the form’s module. While VBA doesn’t pass array s into procedures, it does allow yo u to pass objects. The fo rm an d its cont rols are a ll objects. The ru le of th um b is to pass the form w hen you w an t to a ccess any of its controls or pass a specific control w hen y ou w ant to just w ork w ith it. Occasiona lly, yo u m ay even pa ss in both . The project file Contro l.dvb (on supplied CD ) ha s an exa mple of th is situa tion in th e Tabs procedure of th e Ba sGlobal mod ule. The form is passed in to allow the procedure to spin th rough a ll of the controls on the form , but a n individual control (a m ultipa ge cont rol) is passed in for q uick access to its properties. The ro utine could just as easily h ave been w ritten to accept the form a nd th e mu ltipage’s nam e. Then the code w ould ha ve ha d to find th e requested control as it spun through the various controls of the passed-in form. One technique is not a ny better than the oth er—they are just different w ay s of approach ing the design. The synta x of the procedure taking a form a nd control is Public Sub Tabs(Form As UserForm, Ctrl As Control)

One problem th at a rises w hen try ing to ma nipulate a form or its controls from a procedure tha t is outside the form’s modu le is that you don’t get the benefit of the Intellisense design feature in the outside module as yo u design. This mean s that y ou n eed to kno w the o bject’s properties an d meth ods to access them (no hints as you ty pe). One metho d of getting around this problem is to design the module inside the form procedure and then m ove it over to the support module. Natura lly, you w ill 311

AutoCAD VBA Programming

Recommend Documents