Refering to Public Variables in Functions

[Guest]

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

 

[Bob Phillips]

I have to admit to finding this all confusing, but I will try and give some
help.

You call the functions with an argument, but the functions themselves do not
have an argument. This is invalid, assuming I am reading it correctly.

You say that you dimension cell in example 2, but I cannot see where.

You have two instances of the function, on the same workbook?

Anyway, you don't need to, should declare cell as a public variable, just
use code like

Sub A()
Dim cell As Range
Dim sh As Worksheet

Set sh = Activesheet

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

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

It may be better in the function to use a different value than cell, for
maintainability, say

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

BTW I use cedll (lower-case c) as a variable name all the time.

 

[Guest]

So I can say CellIsHidden (Cell) or CellIsHidden Cell. Correct? But If I
want o wrap an If stmt aroung this I am forced to put the variable in
brackets. Correct?

If CellIsHidden (Cell) Then
'so something
End if

I have one more question but will wait for your response to this.

Thanks

 

[Bob Phillips]

No, CellsHidden(Cell) gives an error.

You do however enclose in brackets if you just do an if,

If CellsHidden(cell) Then ...

or return the function to a variable

myVal = CellsHidden(cell)