How public is public?

[K_Macd]

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

 

[jamescox]

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, no
the value it has. An integer (or any numeric variable) to which a valu
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 Su
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 use
will never again be presented with the input box - unless you set CH_Ro
back to zero once you do whatever ColsHide is supposed to do (or hide).

Hope this helps! :Bg

 

[K_Macd]

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