English

How To: Use VBA functions in the Field Calculator

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.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/22606' target='_blank'>How To: Use ArcMap to concatenate fields in a table</a>
  • Convert a string to proper case.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/31541' target='_blank'>How To: Convert a string to proper case in the ArcMap Field Calculator</a>
  • Round numbers of attribute table to the nearest nth value.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/31778' target='_blank'>How To: Round numbers of an attribute table to the nearest nth value</a>
  • Create a sequential unique ID field.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/27427' target='_blank'>How To: Create a sequential unique ID field equivalent to the ArcView 3.x field calculator expression rec+1</a>
  • Convert Decimal Degree values to Decimal Minute Seconds.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/31243' target='_blank'>How To: Convert Decimal Degree values to Degree Minute Seconds values using the Field Calculator</a>
  • Convert Degrees Minutes Seconds values to Decimal Degree values.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/22455' target='_blank'>How To: Convert degrees minutes seconds values to decimal degree values using the Field Calculator</a>
  • Convert Decimal Minutes to Decimal Degrees.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/28520' target='_blank'>How To: Convert Decimal Minutes to Decimal Degrees using the Field Calculator</a>
  • Replace one value for another within a field.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/20119' target='_blank'>How To: Replace one value for another within a field</a>
  • Truncate a field.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/20856' target='_blank'>How To: Truncate a field</a>
  • Populate a date field with unique values.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/23194' target='_blank'>How To: Populate a date field with unique values</a>
  • Calculate a date field to be the current date.
    <a href='http://support.esri.com/en/knowledgebase/techarticles/detail/23213' target='_blank'>How To: Calculate a date field to be the current date</a>

Related Information