View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ExcelMonkey ExcelMonkey is offline
external usenet poster
 
Posts: 553
Default Refering to Public Variables in Functions

I have a routine which loops through cells in a workbook and looks for
certain items. I loop through the cells by doing the following:

For Each Cell in sh.UsedRange
'Use Function to check for certain things
CellIsHidden(Cell)
Next

I have dimensioned a variable "Cell" As Public (should probably change
name). However, I have been sloppy in my coding and in some of my functions
I refer to the public name itself "Cell" (See Example 1). And in others I
have called the functions using the public name but then dimensioned the
variable using a different name within the function itself (See Example 2).

Example 1
Sub Main ()
CellIsHidden(Cell)
End Sub

Public Function CellIsHidden()
If Cell.Parent.Protect = True Then
If Cell.FormulaHidden = True Then
CellIsHidden = True
End If
End If
End Function

Example 2
Sub Main ()
CellIsHidden(Cell)
End Sub

Public Function CellIsHidden(rng As Range)
If rng.Parent.Protect = True Then
If rng.FormulaHidden = True Then
CellIsHidden = True
End If
End If
End Function

I have not been clear on what the implications are of my mixed methodogies.
But I am now starting to have problems. Example 2 above works. Example 1
Creates a Run Time Error 13. Also, when I run my macros, I am somehow
protecting sheets unknowingly. It has been recommened to me that I should
change the name of my public variable from "Cell" to something else. Aside
from that, do other issues arise when you have a public variable and you
chose not to refer to it by its public name as in Example 2? Should I change
the name and then stick with one methodology (i.e. Example only).

Thanks