Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to Public Variables in Functions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
public variables | Excel Discussion (Misc queries) | |||
Public variables | Excel Discussion (Misc queries) | |||
Public Variables | Excel Programming | |||
Public Variables | Excel Programming | |||
Public Variables | Excel Programming |