VBA Magic


Print Friendly, PDF & Email

Useful EXCEL VBA Commands:

Most of these commands work in EXCEL and ArcGIS Field Calculator.  ESRI support has a nice description of these commands in the context of the Field Calculator here and some examples here.

  • left: The Microsoft Excel LEFT function allows you to extract a substring from a string, starting from the left-most character.
    LEFT( text, [number_of_characters] )
  • right:  The Microsoft Excel RIGHT function extracts a substring from a string starting from the right-most character.
    RIGHT( text, [number_of_characters] )
  • mid:  The Microsoft Excel MID function extracts a substring from a string (starting at any position).
    MID( text, start_position, number_of_characters )
  • len: The Microsoft Excel LEN function returns the length of the specified string.
    LEN( text )
  • find – EXCEL:  The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.
    FIND( substring, string, [start_position] )
  • instr – ArcGIS (see ESRI links above): Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
    InStr([start, ]string1, string2[, compare])
  • concatenate – EXCEL: The Microsoft Excel CONCATENATE function allows you to join 2 or more strings together.
    CONCATENATE( text1, [ text2, ... text_n ] )
  • concatenate – ArcGIS: The ArcGIS CONCATENATE function allows you to join 2 or more strings together.  Its syntax, however, is slightly different.  Instead of the concatenate key word, use the & to field entries and text.
    ( [field1] &  [field2] & "

    text

    " & ...  )
  • ucase: The Microsoft Excel UCASE function converts a string to all upper-case.
    UCase( text )
  • lcase: The Microsoft Excel LCASE function converts a string to lower-case.
    LCase( text )
  • replace – EXCEL: The Microsoft Excel REPLACE function replaces a sequence of characters in a string with another set of characters.
    REPLACE( old_text, start, number_of_chars, new_text )
  • replace – ArcGIS: The ArcGIS REPLACE function replaces a sequence of characters in a string with another set of characters.
    REPLACE( old_text or [field], text_to_replace, new_text )