What did I miss? (get constant value from the string text)

[ValerieT]

Hello

I'm using constants to set column number.
I've got a form to help me debug. I want to be able to select (in a combo box) the name of a constant, so that it returns its value. I've tried the function in a sub test, but it is not working...

I usually do stupid syntax/logic mistakes, but this time I can't find what is wrong..

'CONSTANTS
Public CAVA As Long                     
Public CAWF As Long                      
Public CBND As Long                      

Public My_Col
Public My_Test

Public Function GetCol() ' SET CONSTANT VALUE (Column number)
On Error Resume Next
CAVA = Sheets("DATA").Range("CAVA").Column
CAWF = Sheets("DATA").Range("CAWF").Column
CBND = Sheets("DATA").Range("CBND").Column
' a lot more
End Function

Public Function GetConst(sConst As String) As Variant 'GET CONSTANT VALUE WITH STRING NAME

GetCol
Select Case sConst
Case "CAVA": MyCol = CAVA
Case "CAWF": MyCol = CAWF
Case "CBND": MyCol = CBND
'a lot more
End Select
End Function

Sub test()
GetCol
'My_Test = CWLO
'Sheets("DATA").Cells(2, 1) = My_Test 'OK
GetConst (CTID)
Sheets("DATA").Cells(3, 1) = MyCol  'NOTHING
End Sub

[GTO]

'CONSTANTS
Public CAVA As Long
Public CAWF As Long
Public CBND As Long

...are not Constants; they are variables.

I think you maybe are having a bit of a time with the Function GetConst(), as rather than assign a return for the Function, you are assigning a value to the variable MyCol.

In plain English, could you describe just what you would have liked the testing to have achieved?

Hope to help and don't you give up, you're close :-)

Mark

[westconn1]

just nit picking, none of your constants are constants, they are global variables, constants can not be assigned values at runtime

Sheets("DATA").Cells(3, 1) = MyCol 'NOTHING

you have no global variable by the name of mycol

anyway the correct way to use a function, is to return the value from the function to the caller like

Public Function GetConst(sConst As String) As Variant 'GET CONSTANT VALUE WITH STRING NAME
     
    GetCol 
    Select Case sConst 
    Case "CAVA": MyCol = CAVA 
    Case "CAWF": MyCol = CAWF 
    Case "CBND": MyCol = CBND 
         'a lot more
    End Select 
    getconst = mycol
End Function 
 
Sub test() 
    GetCol 
   
    Sheets("DATA").Cells(3, 1) = GetConst ("CTID") 
End Sub

there should be no need to call getcol in both procedures

[ValerieT]

Thank you both for answering. I apologize, I learn by myself both english and VBA.

GTO: If you mean there is no point to go through MyCol, you are right, it was a step in my debug as I didn't know how it works (first time I write function)

West, you're right, they are variable not constants... I've tried your code and it is OK. Now if I can spend a little bit of your time, I've adapted the code to my real use, inside a form:

Form name = Checks
ComboBox name = ListCons
AnswerTexbox= ColPos

Private Sub UserForm_Click()
Load Checks
With Checks.ListCons
.AddItem "CAVA"
.AddItem "CAWF"
.AddItem "CBND"
End With

Me.ColPos = GetConst(ListCons.Value)
End Sub

It works but I have to click on the form to update the fields (show the list, or refresh the results). I guess I didn't put the code at the proper place?
Should I say it? It is my first interactive form too!

[Bob Phillips]

Private Sub ListCons_Change() 
    Load Checks 
    With Checks.ListCons 
        .AddItem "CAVA" 
        .AddItem "CAWF" 
        .AddItem "CBND" 
    End With 
     
    Me.ColPos = GetConst(ListCons.Value) 
End Sub

[ValerieT]

That easy !
Thanks a million

[westconn1]

you should try to be consistant
you use checks.listcons and me.colpos
both are correct, but should use same for both
as the code is in the userform code page, and both are controls in the userform, you could also omit both

[ValerieT]

Westconn1: Not sure I understand but I admit I don't always know what I am doing :-) just adapting internet sample....

me.colops was the way I found to successfully write in colpos.. you mean that checks.colpos would work too? or just colpos?

[westconn1]

you mean that checks.colpos would work too? or just colpos?

both, as long as the code is in the cope page of the Checks userform. me refers to that form, controls in that form do not require the form prefix, so all 3 will work
if the code is in someother module, colpos will require the checks (form name) prefix, me would not be valid and colpos would also not be valid

you know what works now, change and test to see what else works