Knowledge Base - Technical Articles


Technical Article   HowTo:  Use VBA functions in the Field Calculator

Article ID: 31807
Software:  ArcGIS - ArcEditor 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1 ArcGIS - ArcInfo 9.0, 8.3, 9.1, 9.2, 9.3, 9.3.1 ArcGIS - ArcView 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1
Platforms: N/A

Summary

Instructions provided describe how to use the Field Calculator to use some common VBA functions.

The Field Calculator dialog box allows for calculating the values of a field by specifying a calculation expression. You can type the expression directly into the box and also add fields, functions and operators into the box by clicking on them in the dialog box. You can include any Visual Basic number, string or date functions in the calculation expression. Commonly used functions are listed in the dialog box for the current choice of data type. Simple Field Calculator expressions are entered into Field Calculator directly. More complex expressions, such as multi-line scripts, looping and branching, are entered in the Pre-Logic VBA Script code box by clicking the Advanced check box on the Field Calculator dialog box.

For more information on using the Field Calculator, consult the ArcGIS help documentation. To access this documentation:

- Open the Field Calculator dialog box and click the Help button.
- Open the ArcGIS Desktop Help and go to the Index Tab > Calculating > Fields in Attribute Tables > Making Field Calculations.

There is also a help topic for working with date fields in the Field Calculator:
-Open the ArcGIS DT Help and go to the Index Tab > Calculating > Fields in Attribute Tables > Working with Date Fields.

Procedure

This document looks at some common VBA functions: LEFT, RIGHT, MID, INSTR, LEN, REPLACE, CHR, & OPERATOR and IF...THEN...ELSE statements. VBA has a very large array of components that can be used in the Field Calculator. These example are only a few possibilities.

Note: In the VBA function syntax below, optional parameters are shown in square brackets ([ ]). In the examples, field names are shows in square brackets.

  • Left Function

    Returns a Variant (String) containing a specified number of characters from the left side of a string.

    Syntax

    Left(string, length)

    Left Function Example

    MyStr = Left([MyField], 1)

  • Right Function

    Returns a Variant (String) containing a specified number of characters from the right side of a string.

    Syntax

    Right(string, length)

    Right Function Example

    MyStr = Right([MyField], 1)

  • Mid Function

    Returns a Variant (String) containing a specified number of characters from a string.

    Syntax

    Mid(string, start[, length])

    Mid Function Example

    MyString = "Mid Function Demo" ' Create text string.
    FirstWord = Mid(MyString, 1, 3) ' Returns "Mid".
    LastWord = Mid(MyString, 14, 4) ' Returns "Demo".
    MidWords = Mid(MyString, 5) ' Returns "Function Demo".

  • Len Function

    Returns a Variant (Long) containing the number of characters in a string.

    Syntax

    Len(string)

    Len Function Example

    MyString = [MyField] ' Initialize variable.
    MyLen = Len(MyString)

  • InStr Function

    Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

    Syntax

    InStr([start, ]string1, string2[, compare])

    InStr Function Example

    MyPosition = InStr([address], " ")

  • Replace Function

    Returns a string in which a specified substring has been replaced with another substring a specified number of times.

    Syntax

    Replace(expression, find, replace[, start[, count[, compare]]])

    Replace Function Example:

    NewString = Replace([comments], "#", "!")

  • Chr Function

    Returns a String containing the character associated with the specified character code. Note: a link to an external ASCII character chart is included in the Related Information section at the bottom of this page.

    Syntax

    Chr(charcode)

    Chr Function Example

    ' Replace a carriage return character with an exclamation
    NewString = Replace([comments], chr(13), "!")

  • & Operator

    Used to force string concatenation of two expressions.

    Syntax

    result = expression1 & expression2

    & Operator Example

    MyStr = [MyField1] & " " & [MyField2]

  • If...Then...Else Statement

    Conditionally executes a group of statements, depending on the value of an expression.

    Syntax

    If condition Then [statements] [Else elsestatements]

    Or, you can use the block form syntax:

    If condition Then
    [statements]
    [ElseIf condition-n Then
    [elseifstatements] ...

    [Else
    [elsestatements]]
    End If

    If...Then...Else Statement Example

    Number = 53 ' Initialize variable.
    If Number < 10 Then
    Digits = 1
    ElseIf Number < 100 Then
    ' Condition evaluates to True so the next statement is executed.
    Digits = 2
    Else
    Digits = 3
    End If

    Below are examples of Field Calculator expressions that use the above VBA functions, plus many more.

  • Concatenate fields in a table. -show me-
    Summary
    Instructions provided describe the necessary steps to concatenate values from two or more fields in a table using ArcMap.
    Procedure
    1. Start ArcMap
    2. Add a dataset or stand alone table to ArcMap.
    3. Right-click the layer or table in the Table of Contents.
    4. Select the 'Open Attribute Table' or 'Open' option, depending on the data source.
    5. Select 'Options > Add Field.'

       It is possible to use an existing field, but the output of this process will overwrite existing data in that field.

    6. Define the new field in the 'Add Field' dialog box and click OK.
    7. Right-click the field name of the new field.
    8. Select 'Field Calculator.'
    9. Click 'Yes' to the following prompt:



      [O-Image] Calculate values
    10. Type the following expression, where [Field Name] is the name of each individual field to be concatenated:

      [Field Name] & " " & [Field Name] & " " & [Field Name] 
      


       This expression adds a space between the values that are being concatenated. For further information on the use of the concatenation operator, see the Microsoft Visual Basic Editor Help files. In ArcMap, select Tools > Macros > Visual Basic Editor. In the Visual Basic Editor, click Help > Microsoft Visual Basic Help. Click the Index tab, type "concatenation operators", and click 'display.' Select '& operator' and click 'display.'

    11. Click OK.
  • Convert a string to proper case. -show me-
    Summary

     The procedure below is for ArcGIS 9.3.1 or earlier versions. Starting at ArcGIS 10.0, expressions in the Field Calculator are created using only VBScript or a standard Python format.


    Instructions provided describe how to use the ArcMap Field Calculator to convert an upper case, lower case or mixed case string to proper case. For example, a string that is in the following formats:

    "hello world"
    "HELLO WORLD"
    "hELLO wORLD"

    The VBA function "StrConv" converts the string to: "Hello World".

    For information on how to do the equivalent steps in a label expression, please see the link in the Related Information section below.
    Procedure
    1. Add a new text field to the attribute table to store the new string values.
      -show me-
    2. Open the ArcMap Field Calculator for the field created in the step above by right-clicking the new field name heading in the Table View and click Calculate Values.
    3. In the Field Calculator dialog box, type the following code in the text box under "Your Field Name = "

      StrConv([Existing Field], vbProperCase)


      Change [Existing Field] to match the name of the existing field that contains the string values that are not currently in the proper case.
    4. Click OK in the Field Calculator dialog box.
  • Round numbers of attribute table to the nearest nth value. -show me-
    Summary
    Instructions provided describe how to round values in an attribute table to the nearest nth value using Field Calculator.
    Procedure
    1. Open attribute table.
    2. Right-click field heading and select Calculate Values.
    3. Place a check in the Advanced checkbox on the field calculator.
    4. Paste the following code into the Pre-Logic VBA Script Code box.


      Dim dblRnd as Double 
      
      dblRnd = math.round([Field]/N)*N

    5. Replace [field] with field to be rounded and N with value to round the values too.

      Nearest Half N = 0.5
      Nearest Tenth N = 0.1
      Nearest Hundredth N = 0.01
    6. In the final dialog box type "dblRnd" without the quotes.
    • Use VBA functions in the Field Calculator
      Instructions provided describe how to use the Field Calculator to use some common VBA functions. The Field Calculator dialog box allows for calculating the values of a field by specifying a calculation expression. You can type the expression di...
  • Create a sequential unique ID field. -show me-
    Summary
    In ArcView 3.x, typing 'rec+1' in the field calculator creates a sequential unique ID field for a layer. Instructions provided describe how to do the equivalent in the ArcMap field calculator.
    Procedure
    1. Add the number field.
      -show me-
    2. Click Start Editing on the Editor toolbar.
    3. Right-click the new number field. Click Calculate Values.
    4. Click the Advanced check box.
    5. Copy the following code into the Pre Logic VBA Script code:

      Static rec As Long 
      
      Dim pStart As Long
      Dim pInterval As Long

      ' adjust start value if you want it to
      ' start at a value other than 1.
      ' For example, 1000.
      ' ====================================

      pStart = 1

      ' adjust interval value if you want it to
      ' increment at a value other than 1.
      ' For example, 2.
      ' =======================================

      pInterval = 1

      If (rec = 0) Then
      rec = pStart
      Else
      rec = rec + pInterval
      End If

    6. Type the word 'rec' in the text box under [Your Field Name] =.
    7. Click OK.
  • Convert Decimal Degree values to Decimal Minute Seconds. -show me-
    Summary
    Instructions provided describe how to use the Field Calculator to convert Decimal Degrees stored in a Numeric field to Degrees Minutes Seconds stored in a Text field. The output by default will be in the following format:

    DDºMM'SS.SS"N
    Procedure
    Follow the steps below:

    1. Add the table to ArcMap.
    2. Right-click on the Table in the Table of Contents and select Open.
    3. Verify 'Edit' mode is not enabled. Click the Options button and select Add Field.
    4. Enter DMSLat in the Name field and select Text from the Type drop-down list. If DMSLat is already used as a field name, select a name that is not being used.
    5. Change the length to 20.
    6. Right-click on the DMSLat field and select Calculate Values.
    7. Click Yes, if presented with a message box.
    8. Check the Advanced check box.
    9. Paste the following code into the expression box:

      Dim DDField
      
      Dim zM1 As Double
      Dim Suffix As String
      Dim DMS As String
      Dim zY As Double
      Dim zD As Double, zM As Double, zS As Double
      Dim Dchr As String, Mchr As String, Schr As String
      Dim Degree As String, Minute As String, Second As String
      '=================================================
      'Adjust the variables below
      DDField = [DDLat] 'Change to field with decimal degree values
      Dchr = Chr(186) 'Character after degrees
      Mchr = Chr(39) 'Character after minutes
      Schr = Chr(34) 'Character after seconds
      '=====================================================
      zY = DDField
      If zY >= 0 Then
      Suffix = "N" 'N if Latitude, E if Longitude
      Else
      Suffix = "S" 'S if Latitude, W if Longitude
      End If
      zY = Abs(zY)
      zD = Int(zY)
      Degree = CStr(zD)
      zM = (zY - zD) * 60
      zM1 = Int(zM)
      Minute = CStr(zM1)
      zS = FormatNumber(((zM - zM1) * 60), 2)
      zS1 = Int(zS)
      Second = CStr(zS)
      DMS = Degree & Dchr & Minute & Mchr & Second & Schr & Suffix

    10. Change the value within the brackets next to 'DDField =' to the field in the table that contains the latitude decimal degree values. To change the characters after degrees minutes and seconds edit the value after 'Dchr =', 'Mchr =', and 'Schr =' respectively.
    11. Paste the following code into the 'DMSLat =' box at the bottom of the dialog box.

      DMS

    12. Click OK to run the Field Calculator.
    13. Repeat steps 3 through 12 for the longitude values, but change the values within the code where Suffix = "N" to Suffix = "E" and where Suffix = "S" to Suffix = "W". Also, change the value next to 'DDField =' to the field in the table that contains the longitude decimal degree values.
  • Convert Degrees Minutes Seconds values to Decimal Degree values. -show me-
    Summary

     The procedure below is for ArcGIS 9.3.1 or earlier versions. Starting at ArcGIS 10.0, expressions in the Calculator are created using only VBScript or a standard Python format.

    Instructions provided describe how to use the Field Calculator to convert Degrees Minutes Seconds stored in a string field to Decimal Degrees stored in a number field. The values must be stored in a field in a table as Degrees Minutes Seconds with no symbols. For example:

    25 35 22.3

    In the example, 25 is degrees, 35 is minutes and 22.3 is seconds.
    Procedure
    Follow the steps below.

     Initially perform the steps below on positive numbers. After the conversion is complete, multiple any fields that need to be negative by -1.


     The format of the records within the Degrees/Minutes/Second fields must be DD MM SS. Any blank records or records with extra spaces or characters, such as single or double quotes, may cause a user interrupted error.



    1. Add the table to ArcMap.
    2. Right-click on the table in the Table of Contents and click Open.
    3. Verify 'Edit' mode is not enabled. Click the Options button and select Add Field.
    4. Enter Lat2 in the Name field and select Double from the Type drop-down list. If Lat2 is already used as a field name, select a name that is not used.
    5. Verify that the Scale and Precision is set to 0 and click OK.
    6. Right-click on the Lat2 field and select Calculate Values.
    7. Click Yes if presented with a message box.
    8. Check the Advanced check box.
    9. Paste the following code into the Pre-Logic VBA box:

      Dim Degrees as Double
      
      Dim Minutes as Double
      Dim Seconds as Double
      Dim DMS as Variant
      Dim DD as Double

      DMS = Split([Latitude])
      Degrees = CDbl(DMS(0))
      Minutes = CDbl(DMS(1))
      Seconds = CDbl(DMS(2))
      DD = (Seconds/3600) + (Minutes/60)+ Degrees

    10. Find the line that begins 'DMS. . .' The text within the brackets [ ] is the name of the field holding the latitude values. Replace the word Latitude in the code with the name of the field that stores the latitude values in the table.
    11. Paste the following code into the 'Lat2 =' box at the bottom of the dialog box.

      CDbl(DD)



      -show me-
    12. Click OK.
    13. Repeat steps 3 through 12 for the longitude values.

       Null values in the DMS field will cause an error when running the VBA code in the Field Calculator.

  • Convert Decimal Minutes to Decimal Degrees. -show me-
    Summary
    Instructions provided describe how to convert decimal minutes data to decimal degrees (DDEG) data. This script works for string decimal minutes values in the format of: <Deg> <space> <Decimal_minutes>.

    For example:
    -10  30.57
    
    100 49.3
    0 25.2789
    45 0
    -2 0.25

    Procedure
    1. Create a new field with data type 'double' to store the DDEG data.
      -show me-
    2. Select Editor > Start Editing from the Editor toolbar.
    3. Right-click on the new field and select Calculate Values.
    4. Click the Advanced check box.
    5. Copy the following code into the Pre-Logic VBA Script code:

      dec_min=[dec_min]
      
      if IsNull(dec_min) or dec_min = "" or IsEmpty(dec_min) then
      dec_deg = 0
      else
      space_pos=InStr(dec_min," ")
      deg=Left(dec_min,space_pos-1)
      min=Right(dec_min,Len(dec_min)-space_pos)
      min=min/60
      if deg < 0 then
      dec_deg=(Abs(deg)+min) * -1
      else
      dec_deg=deg+min
      end if
      end if

    6. On line 1 of the code sample, change [dec_min] to be the name of the field that stores the decimal minutes values.
    7. Type the word 'dec_deg' in the text box under [Your field Name] =.
    8. Click OK.
  • Replace one value for another within a field. -show me-
    Summary
    Instructions provided demonstrate how to use the Replace function in the Field Calculator, to replace one value for another within a field in a table.
    Procedure
    1. Load the table into ArcMap.
    2. Select Start Editing from the Editor menu.
    3. Open the table.
    4. Right-click the desired field.
    5. Select Field Calculator.
    6. Type the following in the expression box:

      replace()

    7. Place the cursor between the parenthesis of the line just typed.
    8. Select the name of the field from the Fields list.
    9. Type a comma after the end bracket surrounding the field name.
    10. Type the value to be replaced and the replacement value; enclose each value in quotation marks and separate with a comma. For example:

      replace([FLD_name],"Wisconsin","WI")

    11. Click OK.
  • Truncate a field. -show me-
    Summary
    The ArcMap Field Calculator allows truncating an existing value. Several Visual Basic string manipulation functions are available for truncating values.

    The example provided in this article uses the Visual Basic Left function to return a specified number of characters from the left side of a string, and truncates after that number.
    Procedure
    1. Right-click the layer in the ArcMap table of contents.
    2. Select Open Attribute Table.
    3. Select Add Field from the Options dropdown list.
    4. Create a new text field.
    5. Start editing.
    6. Right-click the new field name in the attribute table window.
    7. Click Field Calculator.
    8. Insert the following code in the expression box:

      Left( [field_name],#) 

      where [field_name] is the field to truncate and # is the number of characters you want to preserve.
  • Populate a date field with unique values. -show me-
    Summary
    Instructions provided describe how to generate unique date values after adding a new date field to an attribute table.
    Procedure
    1. Start ArcMap.
    2. Right-click on a layer and Select 'Open Attribute Table.'
    3. Select 'Options > Add Field'. Name the field.
    4. Select 'Date' from the 'type' menu. Click OK.
    5. Right-click on the date field.
    6. Select 'Calculate Values'.
    7. Select the 'Date' radio button in the Field Calculator dialog box.
    8. Copy the code below into the text box.

      DateValue("mm/dd/yyyy") 

      or

      DateValue("February 11, 2002")

    9. Click OK.
  • Calculate a date field to be the current date. -show me-
    Summary
    Instructions provided describe how to calculate a date field to the current date using ArcMap.
    Procedure
    1. Open the attribute table that contains the date field.
    2. Right-click the date field and select Calculate Values.
    3. If you are comfortable calculating values outside of an edit session click Yes.
    4. From within the Field Calculator dialog box set the Type radio button to 'Date'.
    5. Set the field calculator equal to:

      NOW()

    6. Click OK on the field calculator to perform the calculation.

Related Information


Created: 9/15/2006
Last Modified: 5/3/2011

Article Rating: (4)
If you would like to post a comment, please login

Comments

By Anonymous - 03/16/2011 6:50 AM

I have suggested related resources/links that can enhance this article. See below.

Agree with previous too. I'd just like a table of list of basic syntax for things like upper to lower case, multi-line labels, etc.

Rating:

By Anonymous - 01/05/2011 11:10 AM

I would like to see a new article that discusses the topic outlined below.

I agree with the first comment. Please just list all of them and the syntax somewhere.

Rating:

By Anonymous - 09/10/2010 6:18 AM

I have suggested related resources/links that can enhance this article. See below.

Why is it apparently so difficult to list the VBA functions and their paramenters -- all of them -- that are supported in the field calculator? I just need a list with syntax.

Rating:

By Anonymous - 05/03/2007 4:12 PM

Other - See details below.

When giving syntax, please describe in full. I found it too hard to guess the simple 'if' statement syntax: "If condition Then [statements] [Else elsestatements]"

Rating:

By Anonymous - 12/07/2006 10:43 AM

The article contains a dead link.

show me links to missing page