VBA从一个整数到另一个整数的整数数组，不需要键入每一个

Question

我正试图找出的正确代码

FormatStrokeArray = Array (120 to 300 step 1)

因为输入数百个整数是疯狂的。

稍后，我将尝试使用来查找我的值是否在该数组内

If IsInArray(FormatStroke, FormatStrokeArray) = True Then
MsgBox ("WORKS")
end if

功能是

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

Answer 1

请尝试理解下一个代码：

Sub testEvaluate()
Dim FormatStrokeArray
FormatStrokeArray = Application.Transpose(Evaluate("row(120:300)")) 'create an array as you need (based 1 array)
Debug.Print Join(FormatStrokeArray, "|") 'see the array in Immediate Window

'play here with integers:
Debug.Print IsInArray(FormatStrokeArray, 300) 'it returns true
Debug.Print IsInArray(FormatStrokeArray, 100) 'it returns false

Debug.Print PositionInArray(FormatStrokeArray, 150) 'it returns 31 (the 31th element)
Debug.Print PositionInArray(FormatStrokeArray, 100) 'it returns -1 (no match)
End Sub
Function IsInArray(arr As Variant, myVal As Integer) As Boolean
Dim mtch
mtch = Application.Match(myVal, arr, True)
If Not IsError(mtch) Then
If mtch = UBound(arr) Then
If arr(UBound(arr)) = myVal Then IsInArray = True: Exit Function
Else
IsInArray = True: Exit Function
End If
End If
IsInArray = False
End Function
Function PositionInArray(arr As Variant, myVal As Integer) As Variant
Dim mtch: mtch = Application.Match(myVal, arr, True)

If Not IsError(mtch) Then
If mtch = UBound(arr) Then
If arr(UBound(arr)) = myVal Then PositionInArray = mtch: Exit Function
Else
PositionInArray = mtch: Exit Function
End If
End If
PositionInArray = -1
End Function

如果有什么不清楚的地方，请毫不犹豫地要求澄清。

Answer 2

我认为您不需要数组。如果你想检查一个范围内的数字，你可以这样做：

isValueInRange检查给定值是否等于或大于minValue(120(和是否等于或低于maxValue。如果是，则返回true，否则返回false。就是这样。

Option Explicit
Private Const const_minValue As Long = 120
Private Const const_maxValue As Long = 300

Public Sub test_isValueInRange()
'this is for testing the result of isValueInRange
Dim v As Long
v = 123
If isValueInRange(v) = True Then Debug.Print "Test 1: OK" Else Debug.Print "Test 1: error"
v = 10
If isValueInRange(v) = True Then Debug.Print "Test 2: error" Else Debug.Print "Test 2: OK"
v = 301
If isValueInRange(v) = True Then Debug.Print "Test 3: error" Else Debug.Print "Test 3: OK"
v = 300
If isValueInRange(v) = True Then Debug.Print "Test 1: OK" Else Debug.Print "Test 1: error"

End Sub
Public Function isValueInRange(valueToCheck As Long, _
Optional minValue As Long = const_minValue, _
Optional maxValue As Long = const_maxValue)
If valueToCheck >= minValue And _
valueToCheck <= maxValue Then
isValueInRange = True
End If
End Function
'One-liner version of above code that uses the const-values for min and max.
Public Function isValueInRange_shortVersion(valueToCheck As Long)
isValueInRange_shortVersion= (valueToCheck >= minValue And valueToCheck <= maxValue)
End Function