Category Archives: ArcGIS VBA

Open Excel with ArcGIS VBA

To open Excel with ArcGIS VBA,

First, add Microsoft Object Library 12.0 in Tools>Reference.

Then use this code:

Dim ExcelApp As Object
On Error Resume Next

Set ExcelApp = GetObject(, “Excel.Application”)

If ExcelApp Is Nothing Then ‘Excel isn’t already open
Set ExcelApp = CreateObject(“Excel.Application”)
End If

ExcelApp.Workbooks.Open (“C:\temp\New Microsoft Office Excel Worksheet.xls”)

ExcelApp.Visible = True


ExcelApp.Range(“A1”).value = 100

ArcGIS VBA Import Symbology to Multiple Layers

Sub CopyRenderer()

Dim pMxDoc As IMxDocument
Dim pMap As IMap
Set pMxDoc = Application.Document
Set pMap = pMxDoc.FocusMap

Dim pFromLayer As IGeoFeatureLayer
Dim pToLayer As IGeoFeatureLayer
Set pFromLayer = pMap.Layer(modCommon.GetLayerIndex(“Line10”))
Dim i As Integer

For i = 30 To 143
If TypeOf pMap.Layer(modCommon.GetLayerIndex(“Line” & i)) Is IGeoFeatureLayer Then
Set pToLayer = pMap.Layer(modCommon.GetLayerIndex(“Line” & i))
Set pToLayer.Renderer = pFromLayer.Renderer
End If


End Sub

Tragedy: Lost All My Files

Here are some tips when you use VBA to write codes:
1. SAVE ALL THE TIME. VBA does not save your codes automatically upon debug. When ArcGIS crashes, all your files are LOST.
2. DO NOT OPEN TWO VBA if possible. Because this is what happened to me: I overwrote I file with the other, and all my changes are lost! A whole day of work!
3. SAVE MODULES UNDER PROJECT, so that individual VBAs that open different projects will not overwrite each other.

Adding a field to the attribute table in ArcGIS

Dim pMyField3 As IFieldEdit
Set pMyField3 = New Field

With pMyField3
.Name = “Topo”
.Type = esriFieldTypeDouble
End With

Dim pTable3 As ITable
Set pTable3 = pFeatureLayer
pTable3.AddField pMyField3

To reverse a collection

Environment: VBA
Function: To reverse a collection

Dim Reversed As Collection
Set Reversed = New Collection ‘DONT FORGET THIS
For i = 1 To Collection.Count ‘Collection Starts with 1!
Reversed.Add(Collection.Item(Collection.Count – i + 1))

VBA Collection: substitute an item at position j

WHAT! VBA collection does not allow you to alter an item!!!
And its Array object is weird. I still have not found out how to use it yet.

To substitute an item in a VBA collection, make this call:

pFeat.Add Item:=”Item”, Before:=j
pFeat.Remove j + 1

Editing Attribute Table

pFeature.Value(pFeature.Fields.FindField(“Topo”)) = 3

Selecting features with Query VBA

Dim pQueryFilter As IQueryFilter
Set pQueryFilter = New QueryFilter

pQueryFilter.WhereClause = “X > 1”

Dim pFeatureSelection As IFeatureSelection
Set pFeatureSelection = pFeatureLayer
pFeatureSelection.SelectFeatures pQueryFilter, esriSelectionResultNew, False
pmxDoc.ActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing

Change symbology with ArcGIS VBA

How do you change the symbology of features in ArcGIS with VBA?
First, we have to work on IGeoFeatureLayer.
Cast the normal IFeatureLayer As IGeoFeatureLayer.
Then we can call the renderer by
IGeofeatureLayer.Renderer = pSimpleRenderer

With pSimpleRenderer
.Label = “State Border”
Set .Symbol = pSFSymbol
End With

and where
With pSFSymbol
.Color = pColor

End With

and where
Dim pColor As IRgbColor
Set pColor = New RgbColor
pColor.RGB = RGB(255, 255, 128)


Dim pDoc As IMxDocument
Set pDoc = ThisDocument
Dim pMap As IMap
Set pMap = pDoc.FocusMap

Dim pLayer As ILayer
Set pLayer = pMap.Layer(0)

Dim pFLayer As IFeatureLayer
Set pFLayer = pLayer
Dim pLyr As IGeoFeatureLayer
Set pLyr = pFLayer

‘Create a color
Dim pColor As IRgbColor
Set pColor = New RgbColor
pColor.RGB = RGB(255, 255, 128)

‘Create a symbol
Dim pSFSymbol As ISimpleMarkerSymbol
Set pSFSymbol = New SimpleMarkerSymbol

With pSFSymbol
.Color = pColor

End With

‘Create a renderer
Dim pSimpleRenderer As ISimpleRenderer
Set pSimpleRenderer = New SimpleRenderer

With pSimpleRenderer
.Label = “State Border”
Set .Symbol = pSFSymbol
End With

‘Set the layer’s renderer
Set pLyr.Renderer = pSimpleRenderer

‘ redraw the TOC and the map