Run Time Error 13 Type Mismatch

[ExcelMonkey]

I am wrapping a function around a public variable called Cell. When I run
the macro, the code breaks with a Run Time Error 13. However when I hit F8
it allows me to step through it. I can't figure out why its breaking in the
first place. Do I have to further dimenion it within the function?


Public Cell As Range
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


Thanks

 

[Jan Karel Pieterse]

Hi ExcelMonkey,

I am wrapping a function around a public variable called Cell. When I run
the macro, the code breaks with a Run Time Error 13. However when I hit F8
it allows me to step through it. I can't figure out why its breaking in the
first place. Do I have to further dimenion it within the function?

1. Don't use Cell for a variable name, better to use some name that describes
what it stands for: rActiveCell as Range
2. You don't assign Cell to any object in your code, e.g.
Set rActiveCell=ActiveCell

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[ExcelMonkey]

Sorry. I did not post all code. Its quite extensive. I am effecitvely
looping through cells in a sheet. The code will work if - upon passing it to
the function - I change all references to "Cell" in the Function as "rng" and
put "rng As Range" in the brackets of the function (i.e. Public Function
CellIsHidden(rng as Range)). This is how I originally had it.

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


But after reveiwing I thought that since "Cell" is a public variable, I
could probably refer to it using its public name instead assigning it a new
variable name within the Function. But this seems to cause problems and
breaks the code.


Public Cell As Range
Sub Main()

For Each Cell in sh.UsedRange
CellIsHidden(Cell)
NExt
End Sub


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

 

[Jan Karel Pieterse]

Hi ExcelMonkey,

But after reveiwing I thought that since "Cell" is a public variable, I
could probably refer to it using its public name instead assigning it a new
variable name within the Function. But this seems to cause problems and
breaks the code.

Why not like this (no need for a public variable: pass as argument):

Sub Main()
Dim rCell As Range

For Each rCell in sh.UsedRange
CellIsHidden rCell
Next
End Sub

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

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com