All I can say is Wow!

Thanks for researching this subject so thoroughly.


Gord

On Sun, 8 May 2011 15:31:27 -0700 (PDT), joeu2004 wrote:

On May 8, 12:40 pm, Gord Dibben wrote:
This is one letter shorter than your first suggestion.

I didn't think of it that way. I don't really count characters,
although I know you were interested in being "more concise".

I tend to measure "conciseness" in terms of number and complexity of
apparent operations, not number of characters.

I thought it would be more significant that Cells(1,mycol) does not
require the concatenation that Range(mycol & 1) does.

But I am surprised to learn that both perform about the same.

Actually, the Range implementation is about 5% faster on my
computer(!). But we're only talking about 0.3 microsec on my computer
(YMMV). And I haven't determined if that is statistically
significant.

Not surprisingly, the implementation below is about 55% faster than
the Cells and Range implementations. But it does not qualify as "more
concise", of course.

I'm surprised that it is "only" 54-66% faster, since it does not
require the interprocess communication that I presume the Cells and
Range implementations do.

I somewhat confirmed that they do by substituting Range("a1").Value,
which presumably must communicate with the Excel process. (Of course,
it does not perform the same functionality. That was not its
purpose.)

Note: Initially, everything appeared to perform about the same. Then
I remembered that VBA adds overhead the first time each code path is
executed for __each__ call(!). So for the final comparison, I looped
20 times within the call and averaged the time ignoring the first
iteration. (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.) Also, I sync'd with the process clock and booted the
system in safe mode without networking in order to minimize extraneous
system activity.

-----

A non-concise, but faster implementation....

Function getcolnum(mycolumn As String) As Long
Const lcA As Long = 97 'Asc("a")
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA + 1
Else
getcolnum = _
(Asc(LCase(Left(mycolumn, 1))) - lcA + 1) * 26 _
+ Asc(LCase(Right(mycolumn, 1))) - lcA + 1
End If
End Function

The second getcolnum expression can be simplified by rearranging
terms, to wit:

Const lcA27 As Long = 27 * lcA
[....]
getcolnum = _
26 * Asc(LCase(Left(mycolumn, 1))) _
+ Asc(LCase(Right(mycolumn, 1))) - lcA27 + 27