HowTo:  Use VBA functions in the field calculator

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 'Calculate Values.'
9. Click 'Yes' to the following prompt:
   
   
   
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.

1. Add a new text field 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 proper case.
4. Click OK in the field calculator dialog box.

• Convert a string to proper case in a label expression
  Instructions provided describe how to use a label expression to convert a string that is upper case, lower case or mixed case to proper case. For example, if a string that is in the following formats: "hello world" "HELLO WORLD" "hELLO wOR...

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

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.

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 using the Field Calculator
  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...

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

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 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 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 expression 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)

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

-10  30.57

100  49.3

  0  25.2789

 45  0

 -2  0.25

1. Create a new field with 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.

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.

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.

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.

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.