I am writing a procedure that I want to either access from the macro manager
in the excel interface (without a parameter) or call from another procedure
and pass a parameter. My first inclination was -

Sub ColsHide(Optional CH_Row As Integer = 1)

which would set the CH_Row variable to 1 if not passed as a parameter.
However this approach makes the procedure invisible to the macro manager.

Next approach was to set a public variable in the calling program -

Public CH_Row As Integer

Sub ColsHideKen()

CH_Row = 1
ColsHide

End Sub

but the following code in ColsHide

If VarType(CH_Row) = vbNull Then
CH_Row = Application.InputBox(Prompt:="Enter row number to test",
Title:="COLS HIDE", Default:=1, Type:=1)


Else
' CH_Row already set publicly

End If

works out that CH_Row exists but the following method cannot interpret it as
a valid numeric input

Cells(CH_Row, CH_Col + 1).Select


TIA
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"

Ken -

You've got a problem in the IF statement: the test you are using

VarType(CH_Row) = vbNull

is checking the _type_ of variable CH_Row has been declared to be, not
the value it has. An integer (or any numeric variable) to which a value
has not yet been assigned has a value of zero.

So, if you meant to check if a value for CH_Row has been set in Sub
ColsHideKen, a more appropriate test would be


If CH_Row = 0 Then

Note that, as you assumed, once CH_Row has been set to zero the user
will never again be presented with the input box - unless you set CH_Row
back to zero once you do whatever ColsHide is supposed to do (or hide).

Hope this helps! :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121274

James

Thanks for your clarrification on data types (too many possibilities
compared to xbase languages !)

However still have an issue in that whilst one can see the public variable
in the watch window any attempt to change it such as

PublicVar = Value

only results in a new variable scoped to that procedure being created but
doesn't alter the public variable

TIA

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"jamescox" wrote:


Ken -

You've got a problem in the IF statement: the test you are using

VarType(CH_Row) = vbNull

is checking the _type_ of variable CH_Row has been declared to be, not
the value it has. An integer (or any numeric variable) to which a value
has not yet been assigned has a value of zero.

So, if you meant to check if a value for CH_Row has been set in Sub
ColsHideKen, a more appropriate test would be


If CH_Row = 0 Then

Note that, as you assumed, once CH_Row has been set to zero the user
will never again be presented with the input box - unless you set CH_Row
back to zero once you do whatever ColsHide is supposed to do (or hide).

Hope this helps! :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121274