How to reference a constant?
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" wrote:
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" wrote:
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" wrote:
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
|