How to reference a constant?

[Sam Kuo]

How do I reference a constant number in VBA? Thanks in advance

'Below is my example code
Private Sub cmdShowMsg_Click
Dim ControlType As Variant
Dim ControlValue 'what data type should this be?
Const ControlDE As Long = 60
Const ControlSF As Long = 70
Const ControlSSF As Long = 80
Const ControlSRPS As Long = 50
Const ControlSRPW As Long = 75
Const ControlFSRPS As Long = 75
Const ControlFSRPW As Long = 95

With UserForm1
For Each ControlType In Split("DE,SF,SSF,SRPS,SRPW,FSRPS,FSRPW", ",")
'how can I reference the ControlValue
'to return the matching constant value, instead of name?
ControlValue = "Control" & ControlType
If .Controls("txtControl" & ControlType).Value <> ControlValue
Then
MsgBox "Contrl value for" & ControlType & " is NOT " &
ControlValue
Else
MsgBox "Contrl value for" & ControlType & " is " &
ControlValue
End If
Next ControlType
End With
End Sub

 

[Joel]

you need to make up an array

Const ControlDE As Long = 60
Const ControlSF As Long = 70
Const ControlSSF As Long = 80
Const ControlSRPS As Long = 50
Const ControlSRPW As Long = 75
Const ControlFSRPS As Long = 75
Const ControlFSRPW As Long = 95
Dim ControlType As Variant
Dim ControlValue As Variant

Dim Controls As Variant

ControlType = Array("ControlDE", "ControlSF", "ControlSSF", "ControlSRPS", _
"ControlSRPW", "ControlFSRPS", "ControlFSRPW")
ControlValue = Array(ControlDE, ControlSF, ControlSSF, _
ControlSRPS, ControlSRPW, ControlFSRPS, ControlFSRPW)

Controls = Array(ControlType, ControlValue)

 

[Sam Kuo]

Hi Joel. Thanks for your help.
Can you please also show me how I should change my For...Next loop to
incorporate the array? Sorry I haven't used an array before..

Thanks
Sam

 

[Joel]

Arrays get address with a parenthsis like MyArray(1). Arrays can start a t
either 0 or 1, I prefer starting at 1.

A = Array(1,2,3)
MyTotal = 0
for i = 0 to ubound(a)
Mytotal = Mytotal + A(i)
next i

or

A = Array(1,2,3)
MyTotal = 0
for each num in A
MyTotal = Mytotal + num
next num

Your code

Private Sub cmdShowMsg_Click()

Const ControlDE As Long = 60
Const ControlSF As Long = 70
Const ControlSSF As Long = 80
Const ControlSRPS As Long = 50
Const ControlSRPW As Long = 75
Const ControlFSRPS As Long = 75
Const ControlFSRPW As Long = 95

Dim ControlType As Variant
Dim ControlValue As Variant

Dim Controls As Variant

ControlType = Array("ControlDE", "ControlSF", "ControlSSF", "ControlSRPS", _
"ControlSRPW", "ControlFSRPS", "ControlFSRPW")
ControlValue = Array(ControlDE, ControlSF, ControlSSF, _
ControlSRPS, ControlSRPW, ControlFSRPS, ControlFSRPW)

With UserForm1
For controlCount = 0 To UBound(ControlType)
ControlValue = "Control" & ControlType

If .Controls("txtControl" & ControlType(CountrolCount)).Value <> _
ControlValue(CountrolCount) Then

MsgBox "Contrl value for" & ControlType & _
" is NOT " & ControlValue
Else
MsgBox "Contrl value for" & ControlType & _
" is " & ControlValue
End If
Next controlCount
End With
End Sub

 

[Sam Kuo]

Hi Joel

Thanks for the detailed explanation with example! I've learnt a lesson

Sam