On May 8, 5:13 pm, "Rick Rothstein"
wrote:
Just out of curiosity, how does this function
compare speed-wise to the function you posted?

It depends on whether the column name is 1 or 2 letters. To a lesser
degree, it depends on whether you are asking about the code fragment
or the function call.

Also, both of our implementations are not as optimal as they could
be. So it depends on whether you want to consider improved
implementations or the original ones.

Note that both the String and the Byte implementations allow for only
up to 676 columns.


Results....

I had expected the Byte implementation to perform much better than
String implementation. I am surprised that is not the case, with one
exception.

I suspect the explanation is: the same engineers that implemented the
Answers Forum also implementated VBA Byte operations. We all know how
incompetent they are ;-). Seriously, I suspect that MS spent some
time optimizing VBA String operations, but not Byte operations.

Note that I am using VBA 6.5.1024 with XL2003 SP3. Perhaps VBA with
XL2007 and later performs differently. However, my understanding is
that MS has not invested in VBA improvements.

That said, it appears that for 1-letter column names, the String code
fragment is 64-65% faster than the Byte code fragment even with
improvements in the Byte implemenation.

For 2-letter column names, the original String code fragment is 3%
faster than the original Byte code fragement. However, the improved
Byte code fragment is 1.5% faster than the improved String code
fragment.

Those numbers are for the code fragment alone. The performance
figures are significantly different for the function call. Recall
that VBA adds significant overhead the first time a code path is
executed __each__ time a procedure is called.

For 1-letter colulmn names, the String function call is still about
49% faster than the Byte function call for both the original and the
improvement implementations.

However, for 2-letter column names, the original Byte function call is
0.8% faster than the original String function call, and the improved
Byte function is 9% faster than the improved String function call.


Measurement Methodology....

In order to measure such small times, time was measured for 1000
iterations around either the code fragment or the function call.
(Actually, 1001 iterations. I always throw away the first iteration
because of first-time anomalies in VBA.) Thus, the measured time
period is 140-740 times longer than the timer calls around it.

Each 1000-iteration time period was repeated 101 times in order to
develop statistics, notably an average and a 95% confidence interval
(CI).

The 95% CI is remarkably narrow. It is mostly less than +/-0.10% of
average when the system was booted in safe mode without networking.
It is mostly less than +/-0.20% of average when the system was booted
normally. The maximum 95% CI is +/-0.44%.

On my computer (YMMV), code-fragment times ran from 0.293878 to
0.927479 microseconds. Function call times ran from 0.557318 to
1.240892 microseconds. These are divided down from the total 1000-
iteration times.


Improved Implementations....

Note: Limited to and optimized for up to 676 columns as were the
original implementations.

String code fragment:

Const lcA As Long = 96 'Asc("a")-1
Const lcA27 As Long = 27 * lcA
[....]
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA
Else
getcolnum = _
Asc(LCase(Right(mycolumn, 1))) + _
26 * Asc(LCase(Left(mycolumn, 1))) - lcA27
End If

Byte code fragment:

Const ucA As Long = 64 'Asc("A")-1
Const ucA27 As Long = 27 * ucA
Dim b() As Byte
[....]
b = UCase(mycolumn)
If UBound(b) = 1 Then getcolnum = b(0) - ucA _
Else getcolnum = b(2) + 26 * b(0) - ucA27