Dutch Gemini's Weblog Pages

February 4, 2011

Weird “error” in localised versions of Excel 2007 on Range.Clear

Filed under: Excel,Programming,VBA — dutchgemini @ 3:29 pm
Tags: , , , , , ,

My customers encounter a weird “error”, although I would call this a “bug”, running my VBA code on their localised versions of Excel 2007 (first it was a German one but since today I can also add Czech).

I never ran into this error during the development so I felt safe and rolled out my application to my customers.

Nevertheless, I went back to the drawing board and started debugging the code but I could not replicate the error —I always work on a English (UK) version of Excel 2007— so I decided to make a few changes to the code adding a LOG trail, sent it to my customer and bingo! the error appeared and I had a trace.

Somewhere in my code I have the following statements:

' Row where data start for reading
'
Private Const READ_INITIAL_ROW = 5
'
' {...some other code...}
'
Private Function ClearBlocks(aiSheet As Worksheet) As Boolean
On Error Resume Next
    Err.Clear
    aiSheet.Range(aiSheet.Cells(READ_INITIAL_ROW, 1), aiSheet.Cells.SpecialCells(xlCellTypeLastCell)).Clear
    ClearBlocks = (Err.Number = 0)
End Function

Guess what?

On the customer’s PC the ClearBlocks function returned ‘False‘ so some kind of error had occurred. As the only statement in this function that could have triggered the error is the ‘.Clear‘ method on the Range, I asked for the error number and description.

To my surprise, Excel persistently raised a “Run-time error '9': Subscript out of range” but at the same time the given range (in our case “$A$5:$AJ$12“) was indeed cleared as expected.

I have now removed the error check in the code and transformed the Function in a Sub and rolled out the updated version, since I do not want to be surprised again by Excel.

Dutch

Advertisements

1 Comment »

  1. […] […]

    Pingback by Subscript buiten bereik werkend met Arrays - Worksheet.nl — June 25, 2012 @ 1:25 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: