Excel VBA and Reference Styles

I encountered something new with Excel reference styles (i.e., A1 vs. R1C1) the other day.  I've mostly used the reference styles when using the Cells.Formula or Cells.FormulaR1C1 properties...for these, you use A1 for the former and R1C1 for the latter, and most importantly, it doesn't matter what reference style you've actually specified in the workbook - Excel just figures it out.

The other day, I tried to use the Range.Validation.Modify function in VBA for the first time.  As is my custom (for ease of programming), I had set the reference style of the workbook to R1C1 before starting.

  

However, not thinking much about it, in the formula of the Modify command, I used A1 style - mostly because Excel had never cared which way I went with the Formula properties, also because I figure this is the style the end user will have.

BIG problem, evil error...I got a nondescript '400' error message.  Debugging showed that the error description was "Application-defined or object-defined error"...big help there.  Hours and hours of Googling were no help.

 

At long last, I thought to check the reference style.  Voila!  When I changed the reference style to R1C1, the code worked!  So, unlike the Formula/FormulaR1C1 property of Cells, apparently Excel will NOT convert your reference style on the fly for the Formula1 property of the Range.Validation.Modify method.

 

So, this wasn't working:

    Dim userValidation As Validation
    Set userValidation = ActiveSheet.Range("C1").Validation

    userValidation.Modify xlValidateList, xlValidAlertStop, _
        xlBetween, "=Lists!$G$2:$G$20"

 

BUT this does:

    Dim userValidation As Validation
    Set userValidation = ActiveSheet.Range("C1").Validation

    userValidation.Modify xlValidateList, xlValidAlertStop, _
        xlBetween, "=Lists!R2C7:R20C7"

 

(when the workbook is set to R1C1 notation)

 

Of course, not knowing what the end user might have, it's necessary to do a check:

Dim userValidation As Validation
Set userValidation = ActiveSheet.Range("C1").Validation

If Application.ReferenceStyle = xlA1 Then

userValidation.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=Lists!$G$2:$G$20"

Else 'ReferenceStyle = xlR1C1

    userValidation.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=Lists!R2C7:R20C7"

End If

 

I hope this saves some other poor person from hours of Googling!  It is really such a simple thing, but it didn't occur to me for so long!

Labeling Supporting Layers in ArcPad

Googling didn't help me much on this topic so I thought it might be worth sharing...regarding labeling background layers for data exported from ArcMap to ArcPad using the ArcPad Data Manager extension ("Get Data for ArcPad" tool) for ArcGIS 10.

The scenario that prompted my discovery was this: I had an ArcMap document that contained the geodatabase feature classes that I needed to check out as well as several supporting layers - parcels, streets, streams, etc.  I wanted the streets to be labeled, but did not need to check them out for editing.  I labeled the streets within the ArcMap file.

Initially I tried exporting the streets as a background shapefile.  However, the labels did not transfer when I opened the exported map file in ArcPad.  After a bit of experimenting I discovered that exporting the streets as a background AXF file kept the labels intact. I suppose in retrospect this does make sense, as the AXF files are supposed to be these wonderful all-encompassing databases.

So there you go, if you want to label your background layers in an export for ArcPad, make sure to export them to an AXF file!

Capturing Button Clicks in ArcObjects

It's been quite a while since I've posted...I've been busily moving myself to North Carolina for a contract position as a GIS Developer with the City of Charlotte.  I'm working through a company called Systemtec.  Everything is going great and Charlotte is a lot of fun!  But with all the commotion involved in moving to a new city, this blog fell to the wayside!

This week, I found something fun in ArcObjects that I thought I should share, as it took a bit of Googling and then some guessing to figure out, so clearly there need to be more posts on the topic!  In particular I found it difficult to find an example of this for VB.net in ArcGIS 10 (rather than 8.3 or VBA) - the syntax is a little different.

My goal in doing this was to determine when the user clicked the Merge button on the Editor menu in ArcMap (for context, I am developing an Editor Extension Add-In for ArcMap 10 using VB.net).  Because Merge works differently from many of the other tools - a bit of experimentation showed that it didn't trigger the OnCurrentTaskChanged event in the Editor - this proved to be a bit problematic.

A post by Kirk Kuykendall set me in the right direction - use the ICustomizationFilter interface.  This allows you to listen for ANY button click (and other things - see the ArcGIS help) and react appropriately.  (Note that the button names you'll need are available here.)  It's designed to prevent the user from doing things you don't want him to do (e.g., accessing VBA, clicking buttons, using tools, etc.).  This is done by setting the result of the function to TRUE - this is one of those tidbits that isn't explicitly stated anywhere that I could see, but a true result from the OnCustomizationEvent function prevents the user from doing whatever it was he was trying to do.

In my case, I didn't want to prevent the user from doing anything, I just wanted to KNOW if he did something.  To do this, just return false after executing whatever code you're interested in, and you can detect the button clicks without affecting the usability of the program.  Only trick is that apparently only one customization filter can be active at a time, so if you have a bunch of different add-ins/dlls running that each has its own filter, that could be a problem.

So enough of that, here's the code I wrote to capture the user clicking the button:

First, create a NEW class module and put in code like this:


Public Class clsCustomizationFilter
  Implements ESRI.ArcGIS.Framework.ICustomizationFilter


  Public Function OnCustomizationEvent(custEventType As _
   ESRI.ArcGIS.Framework.esriCustomizationEvent, eventCtx As Object) As Boolean _
   Implements ESRI.ArcGIS.Framework.ICustomizationFilter.OnCustomizationEvent
   
   If custEventType = ESRI.ArcGIS.Framework.esriCustomizationEvent.esriCEInvokeCommand Then
      Dim cmd As ESRI.ArcGIS.Framework.ICommandItem
      cmd = TryCast(eventCtx, ESRI.ArcGIS.Framework.ICommandItem)
      If cmd.Name = "Editor_Merge" Then
 ImperviousEditorExtension.g_blnDeleteOK = True
      End If
   End If


   Return False


  End Function


End Class

The line ImperviousEditorExtension.g_blnDeleteOK = True is where you would put whatever code you want to have run as a result of the button click.  The "Editor_Merge" name can be replaced with the name (from the link above) of any button you want to monitor.  Note the key "Return False" at the end - including this means that you don't stop the user from completing the merge or whatever else he wants to do.  (If for whatever reason you wanted to stop him from doing something, write a condition to check and within that condition Return True.)

To activate this class, take two steps.  First, assuming you're doing this in an editor extension, in the editor extension class create a global variable:
Public Shared m_Filter As ESRI.ArcGIS.Framework.ICustomizationFilter

(if you're not using an editor extension, put this in whatever root module you have)

Second, put this code somewhere that makes sense:

m_Filter = New clsCustomizationFilter
My.ArcMap.Application.LockCustomization("password", m_Filter)
For example, I included it in my OnStartEditing event handler.

Now, to keep things neat and tidy, I unlocked the customization once I was done, so I included the following line in my OnStopEditing event handler:
My.ArcMap.Application.UnlockCustomization("password")

There you go!  A method to detect the user's button click on any button in the interface.  Hope it helps you as much as it did me!!