LinkBack URL
About LinkBacksI have a very large add-in that I build which is running very slow. In fact
it crashes excel if I run too many times. I am questioning the structure I
have used. I am wondering if the speed has something to do with either:
1) "Public Variables" or
2) the use of "Set" statement
I have been reading about memory leak in the new book by Bullen/Bovey/Green
(page 184) and their comments on memory reclaim. Not sure if it applies here
or not.
The routine effectively:
1) sets up 19 paste ranges, declares a public variable "Cell" and then loops
through every cell in the UsedRange of every sheet in the spreadsheet
(ListAuditResults Sub)
2) tests for 19 attributes (MainAudit Sub) - 1 at a time.
3) If the attribute is found (TRUE) then the routine pastes the cell address
of the variable "Cell" to a named paste range (CellAddressPass Sub) set up
earlier in the routine. This paste range increments on each successive
round. So effectively the routine checks every cell for attribute 1 and then
does it all over again for 2,3,4,5-19.
With regards to the memory leak issue, do I need to do something with the 19
paste range variables to reduce memory leak?
The code below is a rough representation of the routine. I have left out
many details as the does code works - its just slow. Note that I have only
provided details for Case 19 in the MainAudit Sub. All 19 have a Set
statement which increments the paste range for the attribute associated with
each Case.
Thanks in advance!
EM
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Define Public Variables
Public Cell As Range
'Set 19 Past Range Variables
Public Comrng As Range
Public Hardrng As Range
Public Errrng As Range
Public Colrnge As Range
Public Validrng As Range
Public ValidErrrng As Range
Public ExtLinkrng As Range
Public Inputrng As Range
Public CirCellrng As Range
Public CondFormrng As Range
Public FormTyperng As Range
Public FontSizerng As Range
Public FontTyperng As Range
Public NumFormatrng As Range
Public NamedCellrng As Range
Public SpecSearchrng As Range
Public FontIntColrng As Range
Public ProtectedCellrng As Range
Public ColouredFontCellrng As Range
Public HiddenRowColrng As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Main Loop Structure
Dim ObjFind As String
Dim PasteStartCell As String
With Workbook
PasteStartCell = Range("B2").Address
'Set Statements for 19 paste targets let routine know where to print results
Set Comrng = .Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,0)
Set Hardrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,1)
Set Errrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,2)
Set Validrng=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,3)
Set ValidErrrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,4)
Set ExtLinkrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,5)
Set Inputrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,6)
Set CirCellrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,7)
Set CondFormrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,8)
Set FormTyperng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,9)
Set FontSizerng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,10)
Set FontTyperng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,11)
Set NumFormatrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,12)
Set NamedCellrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,13)
Set SpecSearchrng
=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,14)
Set FontIntColrng
=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,15)
Set ProtectedCellrng
=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,16)
Set ColouredFontCellrng
=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,17)
Set HiddenRowColrng
=.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,18)
For Each sh In ActiveWorkbook.Worksheets
For AuditTypes = 1 To 19
For Each Cell In sh.UsedRange
Call MainAudit(ChkbxCtrlFind)
Next
End Select
Next
Next
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub MainAudit(X As Integer)
Select Case X
Case Is = 1
Case Is = 2
Case Is = 3
Case Is = 4
Case Is = 5
Case Is = 6
Case Is = 7
Case Is = 8
Case Is = 9
Case Is = 10
Case Is = 11
Case Is = 12
Case Is = 13
Case Is = 14
Case Is = 15
Case Is = 16
Case Is = 17
Case Is = 18
Case Is = 19
IF Cell............................Then
Call CellAddressPass(HiddenRowColrng)
AdjustedIncrement = Increment(PasteRowIncrement)
Set HiddenRowColrng = HiddenRowColrng.Offset(AdjustedIncrement, 0)
End if
End Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CellAddressPass(rng As Range)
Dim a As String
Dim b As String
a = Cell.Parent.Name & "!" & Cell.Address(0, 0)
b = "'" & Workbooks(OriginalWorkbook).Path & "\[" &
Workbooks(OriginalWorkbook).Name & "]" & _
Cell.Parent.Name & "'!" &
Cell.Address(0, 0)
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", _
SubAddress:=b, _
TextToDisplay:=a
End Sub
Register To Reply
Bookmarks