Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a constant?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a constant?
Hi Joel
Thanks for the detailed explanation with example! I've learnt a lesson :-) Sam "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference array constant values in a formula | Excel Discussion (Misc queries) | |||
Array Constant: How do I reference each value in a formula | New Users to Excel | |||
How do I set up a constant reference to a cell that moves? | Excel Worksheet Functions | |||
excel uses what technique to keep a cell reference constant when | Excel Discussion (Misc queries) | |||
constant reference to an xla function | Excel Programming |