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