# 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>