Is there a way to put constraints when creating variables in VBA?
Example: dim alfa as integer
Now I'd like Excel to accept only values from 0 up to 99, how can I do that?
many thanks
-
2Validate the value at the time of assignment.Rory– Rory2017-09-12 08:50:10 +00:00Commented Sep 12, 2017 at 8:50
-
Ok, but how? dim alfa as integer then?Andrea Zed– Andrea Zed2017-09-12 09:00:28 +00:00Commented Sep 12, 2017 at 9:00
-
You haven't given any information as to how the variable gets a value.Rory– Rory2017-09-12 09:10:47 +00:00Commented Sep 12, 2017 at 9:10
Add a comment
|
2 Answers
You could validate it like this:
Sub Test()
Dim i As Integer
i = 100
If Not (i >= 0 And i <= 99) Then Err.Raise Number:=6, Description:="Number not valid"
'continue to use i
End Sub
You could also create a new class, which would only accept certain values. In most cases this would be overkill, but here it is:
Put this into a standard module
Sub Test()
Dim specialNumber As SNumber
Set specialNumber = New SNumber
specialNumber.Value = 1
specialNumber.Value = 100 'this line will throw an error
End Sub
Then put this into a class module and name the module SNumber
Private specialNumber As Integer
Public Property Get Value() As Integer
Value = specialNumber
End Property
Public Property Let Value(v As Integer)
If Not (v >= 0 And v <= 99) Then
Err.Raise Number:=6, Description:="Number not valid"
Else
specialNumber = v
End If
End Property
Comments
A good way to do it is to use data validation in Excel.
Sub TestMe()
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=AND(A1>=0, A1<=100, ISNUMBER(A1))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Now A1 would be only between 1 and 100 and it would be a number.
