VBA range selection question

  • Thread starter Thread starter Pete
  • Start date Start date
If anyone is still reading this thread, here is the generalization of the
function I asked you (Joe) to time test; it will work with XL2007 and XL2010
(and beyond if Microsoft decides to add more columns in the future) as well
as with prior versions...

Function GetColNum(myColumn As String) As Long
Dim X As Long, Multiplier As Long, B() As Byte
B = UCase(myColumn)
Multiplier = 1
For X = UBound(B) - 1 To LBound(B) Step -2
GetColNum = GetColNum + Multiplier * (B(X) - 64)
Multiplier = 26 * Multiplier
Next
End Function

I believe this function (as well as the one I posted earlier for XL2003 and
before) will execute about as fast as is possible due to the use of Byte
arrays and, as such, I expect it to execute (noticeably?) faster than the
String-based version that you (Joe) posted.

Rick Rothstein (MVP - Excel)
 
If anyone is still reading this thread, here is the generalization of the
function I asked you (Joe) to time test; it will work with XL2007 and XL2010
(and beyond if Microsoft decides to add more columns in the future) as well
as with prior versions...

Function GetColNum(myColumn As String) As Long
Dim X As Long, Multiplier As Long, B() As Byte
B = UCase(myColumn)
Multiplier = 1
For X = UBound(B) - 1 To LBound(B) Step -2
GetColNum = GetColNum + Multiplier * (B(X) - 64)
Multiplier = 26 * Multiplier
Next
End Function

I believe this function (as well as the one I posted earlier for XL2003 and
before) will execute about as fast as is possible due to the use of Byte
arrays and, as such, I expect it to execute (noticeably?) faster than the
String-based version that you (Joe) posted.

Rick Rothstein (MVP - Excel)
 
Just out of curiosity, how does this function
compare speed-wise to the function you posted?
Click to expand...

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
 
Wow, I am shocked. It looks like none of the speed I know Byte arrays to
have in the compiled version of VB translates over to the VBA world at all.
I am going to have to learn not to rely so heavily on my past experience
with the compiled version of VB when trying to develop optimized solutions
for Excel's VBA. Thank you very much for running those time trials... they
were a complete eye-opener for me.

Rick Rothstein (MVP - Excel)




"joeu2004" wrote in message

Just out of curiosity, how does this function
compare speed-wise to the function you posted?
Click to expand...

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
 
here is the generalization of the function I asked you
(Joe) to time test; it will work with XL2007 and XL2010
Click to expand...

I would implement the two methods as follows:

Byte method:

Const lcA As Long = 96 'Asc("a")-1
Dim b() As Byte
[....]
b = mycolumn
For k = 0 To UBound(b) Step 2
getcolnum = 26 * getcolnum + (b(k) Or 32) - lcA
Next

String method:

Const lcA As Long = 96 'Asc("a")-1
[....]
For k = 1 To Len(mycolumn)
getcolnum = 26 * getcolnum + _
(Asc(Mid(mycolumn, k, 1)) Or 32) - lcA
Next

Note that I use "Or 32" in place of LCase. I did not measure the
benefit.

I found that the String method significantly outperforms the Byte for
Len(mycolumn) <= 3 (52%, 38% and 3.6% faster). The Byte method
increasingly outperforms the String method for Len(mycolumn) >= 4
(21%, 33% etc faster).

Apparently, the assignment byte=string adds significant overhead to
the Byte method. When I replace it with Dim b(0 to 1) and discrete
assignments of constants to b(0) and b(1) for the one-character case,
the Byte method is about 1.2% faster than the String method.
 
Back
Top