Refering to Public Variables in Functions
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" wrote:
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.
--
HTH
Bob Phillips
"ExcelMonkey" wrote in message
...
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
|