Slow Macro "CheckSum()"

[Fan924]

CheckSum sums a column of Hex numbers. It gets really slow with larger
column sizes.
16k takes 5 seconds,
32k takes 16 seconds,
64k takes over 60 seconds.
Any tips to spead this up. Excel 97.

Sub CheckSum()
Dim r As Range
Dim HexByte As Variant
Dim NewHex As Variant
Dim ChkSum As Variant
Dim FileNameOnly As Variant
ChkSum = 0
RowStart = 1
RowSize = 65536
RowStop = RowStart + RowSize - 1
For Each r In Range("C" & RowStart & ":C" & RowStop).Rows
ChkSum = ChkSum + Val("&h" & UCase(r.Text))
Next 'Next r
Range("D1").Value = Right(Hex(ChkSum), 4)
Beep
End Sub

 

[Charles Williams]

Try getting the data into a variant arry and then processing that
something like this

Sub CheckSum()
Dim r As Range
Dim HexByte As Variant
Dim NewHex As Variant
Dim ChkSum As Variant
Dim FileNameOnly As Variant

dim vData as variant
dim j as long

ChkSum = 0
RowStart = 1
RowSize = 65536
RowStop = RowStart + RowSize - 1
vData=Range("C" & RowStart & ":C" & RowStop).Value2

for j=lbound(vdata) to ubound(vdata)
ChkSum = ChkSum + Val("&h" & UCase(vData(j,1)))
Next j

Range("D1").Value = Right(Hex(ChkSum), 4)
Beep
End Sub


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Fan924]

Wow Charles! Thanks. It now finishes in under a second. Exactly what I
was looking for.