You are here: > ESRI Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - ArcObjects Visual Basic for Application (VBA) forum

Field Calculator Pre-logic VBA script code...   Guy Hoyle-Dodson Aug 05, 2009
Re: Field Calculator Pre-logic VBA script c...   John Hauck Aug 05, 2009
Re: Field Calculator Pre-logic VBA script c...   Christian Degrassi Aug 05, 2009
Re: Field Calculator Pre-logic VBA script c...   Guy Hoyle-Dodson Aug 06, 2009
Re: Field Calculator Pre-logic VBA script c...   Chirag Danech Aug 06, 2009
Re: Field Calculator Pre-logic VBA script c...   John Hauck Aug 06, 2009
Re: Field Calculator Pre-logic VBA script c...   Christian Degrassi Aug 07, 2009
Re: Field Calculator Pre-logic VBA script c...   Guy Hoyle-Dodson Aug 10, 2009
Re: Field Calculator Pre-logic VBA script c...   Christian Degrassi Aug 10, 2009
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Field Calculator Pre-logic VBA script code - Find maxumum 
Author Guy Hoyle-Dodson 
Date Aug 05, 2009 
Message I an utterly frustrated. I cannot figure out how to read the maximum value in a field (array) and populated another field with this max number. Initially I thought this would be simplicity itself, but I'm completelt stumped.

Also, I can't seem to find any other threads that even broach this subject. They have code for the max across fields, but not within a single field. Am I missing something fundamental here?

The following is the best I've come up with, but it will not populate the entire array with the max value. Can anyone Help? 
 
Dim i As Integer             'Loop controller.
Dim varMax As Variant   'Largest value found so far.
Dim varValues As Variant

    varMax = Null         'Initialize to null
    varValues = Array( [ET_STATION])

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author John Hauck 
Date Aug 05, 2009 
Message What type of field is it? Couldn't you just sort the field and grab the first value? 
 
Public Sub TableSort()
    Dim pMxApp As IMxApplication
    Dim pMxDoc As IMxDocument
    Dim pFeatureLayer As IFeatureLayer
    Dim pFeatureClass As IFeatureClass
    Dim pCursor As ICursor
    Dim pTableSort As ITableSort
    Dim pTable As ITable
    Dim pRow As IRow
    Dim sFieldName As String
    
    sFieldName = "SQMI"
    Set pMxApp = Application
    Set pMxDoc = ThisDocument
    Set pFeatureLayer = pMxDoc.FocusMap.Layer(0)
    Set pFeatureClass = pFeatureLayer.FeatureClass
    Set pTable = pFeatureClass
    Set pTableSort = New TableSort
    Set pTableSort.Table = pTable
    pTableSort.Fields = sFieldName
    pTableSort.Ascending(sFieldName) = False
    pTableSort.Sort pMxApp.Display.CancelTracker
        
    'Create cursor of sorted rows
    Set pCursor = pTableSort.Rows
    Set pRow = pCursor.NextRow
    Debug.Print pRow.Value(pRow.Fields.FindField(sFieldName))
End Sub
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Christian Degrassi 
Date Aug 05, 2009 
Message Hi,

you can also use the IDataStatistics interface and its "statistics" method to obtain the major descriptive stats for your data (if it is numerical)

Help
http://edndoc.esri.com/arcobjects/9.1/ComponentHelp/esriGeoDatabase/IDataStatistics.htm

and example
http://edndoc.esri.com/arcobjects/9.1/ComponentHelp/esriGeoDatabase/DataStatistics_Example.htm
 
  Christian Degrassi, MA
Senior GIS Analyst 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Guy Hoyle-Dodson 
Date Aug 06, 2009 
Message Thanks for your suggestions, but learning how to select the max value from a single field would appear to be the precursor to many other automated selection processes I might want to perform.

The real issue is how to search a single field, perform an operation, and place that value in every record of another field. This seems to be functionally difficult using the field calculator and I'd like to understand why. Does anyone understand this difficulty? Is it an addressing or object hierarchy issue?
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Chirag Danech 
Date Aug 06, 2009 
Message I guess there are two interfaces you may wanna see first. IFields and ICalculator. If you check it on ArcObjects help you would also find sample code for same.

IFields interface would help you to get the reference of the single field which you wanna use for your operation. Offcourse you either should know the index of that field or name in order to retrive.

And ICalculator interface is similar to Field Calculator in ArcMap.

Your workflow should be...

1) Find Single Field
2) Get Max value
3) Apply that next value to every record of other field using ICalculator. 
  Chirag...
GIS Consultant
chirag.danech@gmail.com
LinkedIn : http://www.linkedin.com/in/arcobjectsdeveloper
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author John Hauck 
Date Aug 06, 2009 
Message It's my understanding that the field calculator is processing the values line by line. Therefore when you are looking for something like max value by comparing the current max value against the current value of a given field, the calculated max value could be changing line by line. For example:

dim v
v = Array(1,2,3)
i = UBound(v)

This will always return 2 for i.


dim v
v = Array([OBJECTID])
i = UBound(v)

This will always return 0 for i as the Array always has a single number in it.

If you need to perform calculations like this I would suggest that you use GP scripts or general ArcObjects as you will have more control over how the data is processed.

As with my initial example you could find the value you are looking for then simply use a FeatureCursor to update the values of you other field.

http://resources.esri.com/help/9.3/ArcGISDesktop/ArcObjects/esriGeoDatabase/IFeatureCursor.htm

For this specific scenario GP will also offer solutions to what you are looking for. The UpdateCursor has a SortFields argument that will allow you to sort based on the desired field; you can then take the first value and update each feature in the feature class.

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=UpdateCursor_method

Hope this helps! 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Christian Degrassi 
Date Aug 07, 2009 
Message Hello,

this code calculates the max value for a field and updates another field with it. It run from the field calculator (advanced checkbox activated for VBA code), though I really suggest that in the future you use the VBA macros/editor for this type of processing.

I tested it and it works great. I hope this is what you were looking for.

Make sure you run it on your result field as target and modify the FieldName for the source field and the LayerIndex (you can leave this to 0 if your layer is on the top of the TOC)

Let me know. 
 
Dim pMxDoc As IMxDocument
Dim pFLayer As IFeatureLayer
Dim pData As IDataStatistics

Dim pCursor As ICursor
Dim pStatResults As IStatisticsResults

static flag as long
Dim LayerIndex as long
Dim FieldName as string

static ZMax as Variant

if flag = 0 then
	FieldName = "Z"
	LayerIndex = 0

	Set pMxDoc = ThisDocument
	Set pFLayer = pMxDoc.FocusMap.Layer(LayerIndex)
	Set pCursor = pFLayer.Search(Nothing, False)
  
	Set pData = New DataStatistics
	pData.Field = FieldName
	Set pData.Cursor = pCursor
  	
	Set pCursor = pFLayer.Search(Nothing, False)
	Set pData.Cursor = pCursor
	Set pStatResults = pData.Statistics
	
	ZMax = pStatResults.Maximum

	Flag = 1
endif
__esri_field_calculator_splitter__
ZMax
 
  Christian Degrassi, MA
Senior GIS Analyst 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Guy Hoyle-Dodson 
Date Aug 10, 2009 
Message Yes, this seems to works, although I'm still struggling to understand the code. Actually, it seems to want to grab the max length for that feature, rather than the max value in the length field in the attribute table (I've edited and these are different), although it correctly chooses the max data in other fields.

I like a deeper understanding of how this code works.

For example does

Set pMxDoc = ThisDocument

point toward a particular file in the shapefile directory, or is it directly referencing the attribute file within Arcmap? The dbf file in this file cluster does not appear to contain attribute data, so I assume that this data is stored in another file.

From this, I surmise that pStatResults.Maximum does not look directly at the data in the attribute table, but at some other file that defines the feature?

Does pData.Field = FieldName look for the header within the attribute table? Are these always strings?

Does Set pData.Cursor = pCursor point to the records in the fields and then loops through them using pFLayer.Search?

Any help in understanding this would be appreciated. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Field Calculator Pre-logic VBA script code - Find maxumum 
Author Christian Degrassi 
Date Aug 10, 2009 
Message Hello,

I am assuming you have no experience with VBA and ArcObjects.

Here are some notes for the code.

Cheers 
 
Dim pMxDoc As IMxDocument
Dim pFLayer As IFeatureLayer
Dim pData As IDataStatistics

Dim pCursor As ICursor
Dim pStatResults As IStatisticsResults

static flag as long
Dim LayerIndex as long
Dim FieldName as string

static ZMax as Variant

if flag = 0 then
	FieldName = "Z"
	LayerIndex = 0

	'ThisDocument refers to the current ArcMap Document (the MXD)
	Set pMxDoc = ThisDocument
	'Gets the first layer in the table of content. Assumes that it is a shapefile with your data
	Set pFLayer = pMxDoc.FocusMap.Layer(LayerIndex)
	'Return a cursor to all the features in the layer
	Set pCursor = pFLayer.Search(Nothing, False)
  
	Set pData = New DataStatistics
	'Assigns the field to be analyzed with statistics. The field is a field in the attribute table of the layer. In this case a field with Z values
	pData.Field = FieldName
	'passes the cursor to the DataStatistics object. The DatatStatistics object will take care to scroll through the features/rows in the attribute table and perform the statistics
	Set pData.Cursor = pCursor
  	
	'Retun the statistics
	Set pStatResults = pData.Statistics
	
	'Assigns to ZMax the maximum found value from the field named "Z"
	ZMax = pStatResults.Maximum

	'The Flag variable is static and will persist till the end of the calculations. It will stop the calculator to perform this statistics every time the code is called for each row. The statistics need only to be performed once.
	Flag = 1
endif
__esri_field_calculator_splitter__
ZMax
 
  Christian Degrassi, MA
Senior GIS Analyst