VBA Field Calculator Tips in ArcMap (Kelso)

fieldcalculator2I’ve been relying on the Advanced logic options in ArcMap’s Field Calculator to wrap up Natural Earth Vector. Because of the diacritical (accent) marks present on many placenames around the world, care must be taken to ensure they don’t get corrupted.

With basic SHP files, I often edit thematic data in Excel and then join it back with the SHP. However, Excel mangles diacritical marks, especially if you’re going cross platform between the Mac and PC. Even when everything is setup right, ArcMap the get Info panel displays ? marks for many diacriticals. The attributes table view shows them correctly, though.

Much of the base data and name attributes are compiled in Adobe Illustrator and exported with MaPublisher using custom scripts I’ve written to take the name from the text that’s been grouped with the feature. This allows contributors who don’t have ArcGIS at home or in the office to take part in the project. Because we often have other attributes (maybe a second name, scale rank, type of feature), I use an “_” underscore character to concatenate these into one string. This string then needs to be parsed back into separate attribute columns.

Note: SHP files use Windows 1252 character encoding. If you’re on a Mac, change your MP export options from “System” to that. If you’re on a PC, you’re already good.

Splitting strings:

In VBA prelogic area (advanced checkmark on):

myString = [ColumnName]
myPosition = InStr(myString , “_”)
myLeft = myPosition – 1

myLen = Len(myString)
myRight = myLen – myPosition
country = Left( myString, myLeft )
provNumber = Right( myString, myRight )

In the field results area: provNumber or country.

Note: If the result is destined for a number formatted column, either caste provNumber as a number or use a temporary holding column that is string formatted, then rerun the field calculator on the number formatted column deriving the value from the temp field. ArcMap with auto-caste for you in that case.

After the jump: Find and replace, counting substrings, hit tests, and changing case.

Use Find and Replace when you have wrong value in a column:


result = Replace([ColumnName], “text to find”, “text to replace”)

report result.

If you have extra ” ” space characters, search for them and then evaluate manually:


myString = [ColumnName]
textToFind = " "
countChar = 0
FOR x = 1 TO Len( myString )
   IF  Mid( myString, x, Len(textToFind)) = textToFind THEN
      countChar = countChar + 1

Report countChar.

Finding based on string contents (or if you have bad diacritical mark conversions with ? characters):


IF InStr( [ColumnName], "?" ) > 0 THEN
      result = 1
      result = 0

Report result.

Changing Case:


result = StrConv( [FieldName], vbLowerCase)

Report result.

Variations: result = StrConv( [FieldName], vbUpperCase) and result = StrConv( [FieldName], vbProperCase).

Further reading at ESRI . . .

Tags: , , , , , , , ,

13 Responses to “VBA Field Calculator Tips in ArcMap (Kelso)”

  1. Mike Froese says:

    By just looking you look like an expert on the field calculator.
    I am a newby for the most part. Hopefully my question is and easy one for you!

    I have and object_identifier field with 1,2,3,4….and so on I would like to add these on the back of another field called user_flag that is populated with ELTR in it. So would like to know how to really put 1,2,3,4,and so on on the ends of the ELTR

  2. Bubba says:

    myString = [TEMPOR]
    myExport = [TEMP2]

    For X = 1 To Len(myString)
    If myString = “1” Then
    myExport = “S”
    End If
    Next X

    I have 2590 records/rows in my field. When I use this script it works for changing most of the records but it doesn’t complete them all. I get a “USER INTERRUPT” error and then it terminates.

    Do you know what is wrong?


  3. Nathaniel says:

    @Mike: Use the & character to “concatenate” ELTR & object_identifier fields. If there are leading zeros to preserve make sure new column is text format. You can do this in the regular part of the field calculator, not the advanced part.

  4. Nathaniel says:

    @Bubba: Try running repair geometry on your features. You might have some null polygons in there. Also, if myString is 0 length (empty), you might get this error because of that.

  5. Tiffany says:

    I have calculated values in the attribute table and sorted highest to lowest. I would now like to rank them. I’ve added a new field. How do I calculate sequential numbers to popluate the field? Thanks for any help you can give!

  6. Esther says:

    Hello GIS fellows,
    I am ( a newby) trying to do some ArcMap VBA calculations, but I can not get the script to work (“Error Running VBA Script Code”). I would like to populate a field using an equation that depends on landuse type:

    e.g. if IF [LANDUSE]=”Low Density Residential” THEN (in the Pre-Logic VBA Script Code)
    BOD =( [Ai]/ [Sum_Ai])*( [Rv]/ [Sum_Rvi])*13.5

    e.g. if IF [LANDUSE]=”High Density Residential” THEN (in the Pre-Logic VBA Script Code)
    BOD =( [Ai]/ [Sum_Ai])*( [Rv]/ [Sum_Rvi])*13.5

    Please help

  7. Vince says:

    I have an attribute field (WKZN) that contains numeric values
    >>>>>>>> (integer). I need to filter out the “0’s” (accomplished with a
    > > > > > > Tester) and then write a value in the field to replace the “0’s”. The
    > > > > > > number I need to write needs to be the next highest value from the
    > > > > > > highest existing number in the field (e.g. if 3630 is the largest
    > > > > > > number in the attribute field, I want to write 3631 for my first “0”
    > > > > > > and 3632 for my second “0” and so on…). Any suggestions as to how
    > > > > > > to accomplish this?

  8. shanoboy says:

    Thanks for this. Great help and refresher!

  9. shanoboy says:

    Also, in response to your solution for removing extra spaces. You can use the trim function.

    Trim ( [FieldName] )

    Or if you have too many spaces, you can use replace to shorten them:
    Replace([FieldName], ” “, ” “) <- where you literally replace the excess spaces (” “) with the amount of spaces you desire (” “).

  10. Nathaniel says:

    Thanks for the tip, @Shanoboy!

  11. chris says:

    I am trying to populate a number field in sequential order. I have used the scripts found on this page but it assigns the number in the order that I created each shape. I want it to assign the number in the the order that I have already placed my shapes in. Anyone have any answers??

  12. Dennis says:

    Greetings all,

    I have created a fishnet set of polygons. There is no information in it and I need to populate it with a series of repeating values in each row there are only two possible values and there are three sets so it looks something like this


    While I would normally just fill this in by hand, there are 7000+ blocks. Is there a script out there that will allow me to populate this automatically?

    Thank you all,

  13. Seth says:

    Hi all friend
    I have two field: field1 and field2
    72°4′ 31.192″
    72°3′ 31.195″
    So I want to calculate field2 like this:
    72°04′ 31.192″
    72°03′ 31.195″
    How can I calculate this field2?
    Please tell me about vb Script or Python or other