6. 

How to use a VBA script in the Field Calculator

Attribute table fields can be quickly populated with values for all records using the Field Calculator. For example, one could add a field called AREA to a polygon attribute table and populate every record with its area using an appropriate Visual Basic for Application (VBA) script in the Field Calculator. Since few of us know enough VBA to write our own scripts, it is fortunate that such scripts are widely available and can be loaded into the calculator. One source of scripts is the class network folder, which contains EasyCalc 5 scripts (on the path \Programs\Arc_Extensions\EasyCalculate50\ec\calculate) folder more than 30 scripts (downloaded from the EasyCalc web site) that do everything from calculating line lengths and polygon areas to returning x and y coordinates for points. The function of each script is explained in the web page stored there. There are numerous other sources; a quick Google search on VBA GIS scripts will bring up many and ESRI has many stored and nicely cataloged at their web site.  An ArcUser magazine article that discusses the field calculator and tabulates many simple field calculation is also available.
 

To add a field to an attribute table that will store the calculated values:

• Open the attribute table, click the “Options” button and “Add Field…”. Fill in a name, choose a field type and specify the precision (and scale if needed) (see lecture notes on databases). You will not be able to add a field while in editing mode. Stop editing first.

To populate a field using the field calculator and a stored VBA script:

1) Highlight the attribute table field by clicking on the field name.

2) Right-click on the field name and select “Calculate Values…” to bring up the Field Calculator. If you are not in editing mode, a warning reminds you that changes to the table outside of an editing session cannot be undone. Either answer “No” to “Do you wish to continue?”, start an editing session (Editing Toolbar dropdown>Start Editing) and repeat the steps to continue, or click “Yes” and proceed.

3) With the Field Calculator open, check the “Advanced” check box to see the Prelogic and variable areas that will hold the VBA script.

4) Click the “Load…” button, navigate to the script of interest and click OK to load it.

5) If you’re so inclined, examine the script and marvel at the arcane code and syntax. (More seriously, a great resource for coding in VBA is available in the digital books folder. It should be relatively easy to understand a script by spending just a little time with the tutorial.)

6) Click OK and the field will be populated with new values generated by the script.

Most scripts require that the field properties (long integer, short integer, scale, precision, etc) of the attribute field that you are populating match that required by the script.  If the script does not run or returns values that are nonsense this is the probable cause.

A principle consideration should be the units of the new values. Depending on the script, these will be either in the units of coordinate system of the layer or the units of the Data Frame. Examining the script should establish this. In the example below that calculates area the script uses the properties of the Data Frame to establish units (“pGeometry.Project pMap.SpatialReference”) . Other scripts may rely on the coordinate system of the layer.