If you are attempting at using the function from the spreadsheet side, this
would be known as a UDF or User Defined Function. In this case,
performing any such actions is not allowed as UDFs are merely meant to
return a value, not to perform any actions.
On the other hand, if you are calling on this function from another method
(Sub or Function) within VBA, then performing actions is perfectly legal.
Now, I see 3 main issues with your Function below.
First, the whole purpose of a function unlike a subprocedure is to return a
value (via Let statement, which can be omitted and normally is omitted) or a
reference to an object (via Set statement). I therefore ask you, what is it
that this function is suppose to be returning to the caller of this
function?
To have the function return a value, it needs to be using the following
syntax
<FunctionName> = <Value>
OR
Set <FunctionName> = <Object>
Now onto the 2nd issue I see. What worksheet are you attempting to hide the
rows on? Is this suppose to hide the rows on the parent worksheet of the
"Input_Cell" range object? If so, try the following statement
Input_Cell.Parent.Rows(InputRow+1).Hidden=True
However, the above statement still isn't valid as the argument for the Rows
property is a read only range object, so we much use the Range object
instead. However, we need to modify the above input into the range object
as the above is still invalid. The input must take on the format of the
following in string format.
<FirstRowNumber>":"<LastRowNumber>
Such as the following:
CStr(InputRow+1) & ":" & CStr(InputRow+1)
Therefore, your statement will now look like:
Input_Cell.Parent.Range(CStr(InputRow+1) & ":" &
CStr(InputRow+1)).Hidden=True
Main thing you should be able to take from this, don't leave your code to
the possibility of ambiguity, if reasonably possible. I prequalify just
about all of the various variables, methods, objects and anything else
involved. There's only a few things I don't prequalify, such as the data
type conversion functions cause if I attempt to prequalify them functions,
VBA errors out on me. I learned this ambiguity lesson a long while back the
hard way (years), thus why I'm a stickler on this rule. This is also one
such reason why I use "Option Explicit" at the top of every single module in
VBA. This was one of my first lessons learned when I started working with
VBA.
With prequalifying the various things in code, you not only avoid ambiguity,
but you also avoid the priority issues that you may face, if using this code
on multiple computers. Yes, I have faced that issue head on too. Some
systems would allow me to use "Date" variable of the VBA class, but others
wouldn't allow me to use that statement by itself, so I had to modify the
code to be "VBA.Date", so as it would work on all computers.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
