從整數到整數的VBA整數陣列，無需輸入每一個整數

我正在嘗試找出正確的代碼

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

uj5u.com熱心網友回復：

請嘗試理解下一個代碼：

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

如果有不清楚的地方，請不要猶豫，要求澄清。

uj5u.com熱心網友回復：

我不認為你需要一個陣列。如果你想檢查一個范圍內的數字，你可以這樣做：

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

標籤：

上一篇：如何從另一個陣列的所有元素對陣列進行部分匹配過濾

下一篇：如何使用JQ將陣列合并為物件陣列