Tag Archives: 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


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

Start Indices of VBA Collection and ArcGIS IPointCollection

Note that VBA Collection starts with an annoying 1, while IpointCollection starts with 0. phew.

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