Routine running slow (memory leak?)

E

ExcelMonkey

I 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
 
G

Gary L Brown

1) Always re-set your 'Set's.
What I do is to put the re-set's just before the end of the program along
with an on error stmt so the re-set's don't get hung up. For example...

Sub Test
Set Comrng = .Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,0)
Set Hardrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,1)
''''other code''''
On Error Resume Next
Set Comrng = Nothing
Set Hardrng = Nothing
End Sub

Or, since the ranges may be used throughout the module, put the Set
....=Nothing in the procedure that is run when you no longer need the ranges
defined.

2) If your coding is all within one module, why not use 'Dim' or 'Private'
before the first procedure in the module so that the variable will be known
throughout the module but not carried in memory like 'Public' does.
The 'Public' declaration gives the variable scope in ALL modules.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


ExcelMonkey said:
I 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
Click to expand...
 
R

RB Smissaert

Just one little thing.

Better to do this:

'Set Statements for 19 paste targets let routine know where to print
results
With .Worksheets(AuditShtName).Range(PasteStartCell)
Set Comrng = .Offset(0, 0)
Set Hardrng = .Offset(0, 1)
Set Errrng = .Offset(0, 2)
Set Validrng = .Offset(0, 3)
Set ValidErrrng = .Offset(0, 4)
Set ExtLinkrng = .Offset(0, 5)
Set Inputrng = .Offset(0, 6)
Set CirCellrng = .Offset(0, 7)
Set CondFormrng = .Offset(0, 8)
Set FormTyperng = .Offset(0, 9)
Set FontSizerng = .Offset(0, 10)
Set FontTyperng = .Offset(0, 11)
Set NumFormatrng = .Offset(0, 12)
Set NamedCellrng = .Offset(0, 13)
Set SpecSearchrng = .Offset(0, 14)
Set FontIntColrng = .Offset(0, 15)
Set ProtectedCellrng = .Offset(0, 16)
Set ColouredFontCellrng = .Offset(0, 17)
Set HiddenRowColrng = .Offset(0, 18)
End With

In general the less dots the better.

Or maybe you can do away with all those ranges and just work with an array
or arrays.

RBS

ExcelMonkey said:
I 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
Click to expand...
 
E

ExcelMonkey

So I am still not sure how to address this. The purpose of my question is
based on the idea that my routine may be running slow due to me not resetting
my variables.

I have simplified my example. The goal being to find 3 attributes in a
spreadsheet (say formulas, erros and validation). Upon finding these
attributes, the cell address of the cell they are found in is pasted into a
sheet whose name I have passed to the variable "AuditShtName". Just assume
the sheet exists.

In the first sub, ListAuditResults, I set up the 3 past ranges using the Set
command up front to paste all the cell addresses that I find that exhibits
these 3 attributes. I then run a loop which loops through sheets, within
that loop I loop through the Attributes (1-3) and within that loop I loop
through each cell in the .UsedRange.

Now its not clear to me where I would re-set my paste ranges (in the spirit
of no memory leak) as I need them all the time until the routine ends. If
you look at the sub called MainAudit, every time the Case = 1, I will need
the variable "Hardrng" to have retained its Setting as this tells the routine
where to paste the results upon a test of TRUE. This variable updates every
time the logic test = TRUE. Its this incrementing of +1 each time that
allows the routine to paste to the growing column of pasted values on the
"AuditShtName". I don't think I can put the following statement anwhere in
this sub:

Set Hardrng = Nothing
Set Errrng =Nothing
Set Validrng=Nothing

as these will prevent the sub from knowing where to paste the addresses it
finds. And these cases will be run repeatedly. Each sheet will be evaluted
for all three cases.

Where can I reset my variables? Has my structure prevented me from being
able to do this? That is if I had set it up where it looped through:
attributes/sheets/cells, then I would know that when the first case was
completed, it would never run again and I could put "Set Hardrng = Nothing"
within Case 2. But as I have set it up as sheet/attributes/cells, all the
cases will run and will always need the info in the Set variables associated
with each case.

Any thoughts?

Thanks again

EM


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Define Public Variables
Public AuditShtName As String
Public Cell As Range

'Set 3 Past Range Variables
Public Hardrng As Range
Public Errrng As Range
Public Validrng As Rang
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ListAuditResults ()
Dim ObjFind As String
Dim PasteStartCell As String
Dim sh As Worksheet

With Workbook

PasteStartCell = Range("B2").Address 'in paste sheet

'Set Statements for 3 paste targets let routine know where to print results
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)


For Each sh In ActiveWorkbook.Worksheets
For AuditTypes = 1 To 3
For Each Cell In sh.UsedRange
Call MainAudit(AuditTypes)
Next
End Select
Next
Next
End Wit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub MainAudit(X As Integer)
Dim PasteRowIncrement As Double

PasteRowIncrement = 1

Select Case X
Case Is = 1
If FormulaHasConstant(Cell) Then
Call CellAddressPass(Hardrng)
AdjustedIncrement = Increment(PasteRowIncrement)
Set Hardrng = Hardrng.Offset(AdjustedIncrement, 0)
End If
Case Is = 2
If CellHasError(Cell) = True Then
Call CellAddressPass(Errrng)
AdjustedIncrement = Increment(PasteRowIncrement)
Set Errrng = Errrng.Offset(AdjustedIncrement, 0)
End If
Case Is = 3
If CellHasValidation(Cell) Then
Call CellAddressPass(Validrng)
AdjustedIncrement = Increment(PasteRowIncrement)
Set Validrng = Validrng.Offset(AdjustedIncrement, 0)
End If
End Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function Increment(X As Double)

Increment = 1

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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



Gary L Brown said:
1) Always re-set your 'Set's.
What I do is to put the re-set's just before the end of the program along
with an on error stmt so the re-set's don't get hung up. For example...

Sub Test
Set Comrng = .Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,0)
Set Hardrng =.Worksheets(AuditShtName).Range(PasteStartCell).Offset(0,1)
''''other code''''
On Error Resume Next
Set Comrng = Nothing
Set Hardrng = Nothing
End Sub

Or, since the ranges may be used throughout the module, put the Set
...=Nothing in the procedure that is run when you no longer need the ranges
defined.

2) If your coding is all within one module, why not use 'Dim' or 'Private'
before the first procedure in the module so that the variable will be known
throughout the module but not carried in memory like 'Public' does.
The 'Public' declaration gives the variable scope in ALL modules.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


ExcelMonkey said:
I 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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems Defining Object Variable in For Each Loop 7
Empty Array 4
VBA code in one Module referencing Public Variables Declared inanother Module failing 5
Info Only: Formula Freeze Module 0
Running VB Code 3
Another Atempt 9
Extremely slow run-time 6
Very Slow reading excel data into an array (while opened in new xl 17

Top