Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Macro "CheckSum()"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Macro "CheckSum()"
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Macro "CheckSum()"
Wow Charles! Thanks. It now finishes in under a second. Exactly what I
was looking for. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Will using a "personal macro workbook" slow down my other VBA code | Excel Programming | |||
Sending macro based e-mail with built-in "Heading" and "Text" | Excel Programming |