PDA

View Full Version : [SOLVED] What did I miss? (get constant value from the string text)



ValerieT
07-22-2014, 03:39 AM
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
07-22-2014, 03:52 AM
'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
07-22-2014, 03:59 AM
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 'NOTHINGyou 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
07-22-2014, 06:03 AM
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
07-22-2014, 06:18 AM
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
07-22-2014, 06:41 AM
That easy !
Thanks a million

westconn1
07-22-2014, 02:27 PM
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
07-23-2014, 12:39 AM
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
07-23-2014, 02:41 AM
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