VBA Array of integers from integer to integer without typing out every single one

Question

I am trying to figure out a correct code for

FormatStrokeArray = Array (120 to 300 step 1)

Because typing out hundreds of integers would be insane.

Later I am trying to find if my value is inside that array using

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

and the function is

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

Why do you compare a string (stringToBeFound) to an array filled with integers? — Ike, Jan 18 '22 at 09:07
Because I don't know how to do the code for the goal I am trying to achieve thus I posted a thread about it — Eduards, Jan 18 '22 at 09:10
Don't use an Array, use an ArrayList. You will need a reference to mscorlib or use a late bound creation via 'Sytem.Collections.Generic.ArrayList'. You populate the ArrayList using the .Add method. You can check if a value exists using the .contains method. Be aware that the ArrayList does not give any intellisense and that you may need trial and error for some of the overloaded methods. If you wanted to be braver you could try my Lyst object which has non of these limitations but *IS* a work in progress. You'll need to message me to get the download link. — freeflow, Jan 18 '22 at 10:32
Other quick comments. The Step is not legal syntax for the To when dimensioning arrays. The Array statement (i.e. = Array) takes a list of values and returns a Variant containing an array of the items you provided. To get a string array you would use Dim FormatStrokeArray(80). VBA does allow 120 to 300 but why do you need this rather than an array of 80 items. BY the way, the ArrayList also has a ToArray method which is useful if you need to push your data back into Excel for example. — freeflow, Jan 18 '22 at 10:41
I do realise that, used that just as the means of explaining the logic in somewhat of a code form — Eduards, Jan 18 '22 at 10:55

Ike · Answer 1 · 2022-01-18T09:22:23.420

1

I don't think that you need an array. If you want to check numbers in a range you can do it like this:

isValueInRange checks if the given value is equal or greater than the minValue (120) and equal or lower than the maxValue. If yes it returns true otherwise false. That's it.

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

edited Jan 18 '22 at 09:22

answered Jan 18 '22 at 09:14

Ike

9,580
4
13
29

I would prefer something waaaaaaaay shorter because the task I am describing feels literlally like 1-2 lines of code – Eduards Jan 18 '22 at 09:16
1

You don't need the test-function - only `isValueInRange` - and you can put that on two lines - if you want to. But I prefer readable code over short code :-) – Ike Jan 18 '22 at 09:19
1

added a one liner version :-) – Ike Jan 18 '22 at 09:21
And yeah, I still need it to be an array or string list or whatever because I will have 600 arrays that AFTER creating them I will go through with 150 if functions to check which array has what value. This code in answer suggests that there's one array and checking it right after it – Eduards Jan 18 '22 at 09:21
No - there is no array in this solution. You can use 600 different min/max-combinations - instead of the const-values. You could even put those 600 min/max values on a worksheet - or define them as an 2-dimensionl-array – Ike Jan 18 '22 at 09:24
No I am strictly against any values storing outside the RAM or whatever, programmatically only in other words. And Having 1200 different variables is not handy neither – Eduards Jan 18 '22 at 09:26
Like in the OP I am looking for most optimal and short and logical way to do such integer ranges where I only change the names of arrays and check whatever integers in whatever array. And so I need to fill that array programmatically instead of typing ("1", "2".... thousands of times) – Eduards Jan 18 '22 at 09:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241162/discussion-between-eduards-and-ike). – Eduards Jan 18 '22 at 09:33
Sorry, I don't understand why you don't want to compare integer values to min and max-values. If so - maybe others have other ideas. – Ike Jan 18 '22 at 09:33

score 1 · Accepted Answer · answered Jan 18 '22 at 09:34

1

Please, try understanding the next code:

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

If something not clear, please do not hesitate to ask for clarifications.

answered Jan 18 '22 at 09:34

FaneDuru

38,298
4
19
27

1

@Eduards `PositionInArray` it's a bonus... Just to see how it can be done. Sometimes, you will/may need it. It shouldn't... How did you call the function? Does something like `Debug.Print IsInArray(FormatStrokeArray, ActiveCell.Value)` return an array? Of course, the value must be a number. It shouldn't be a string only looking like a number... – FaneDuru Jan 18 '22 at 09:50
This is exactly what I was looking for, thank you, friend! Didn't need to know the position in array of any item, but thanks for that addition – Eduards Jan 18 '22 at 09:50
I fixed my error, no problems here, but I get that this code counts rows? Which is kind of a workaround – Eduards Jan 18 '22 at 09:53
1

@Eduards Not really... It returns the position of the searched element in the array. It works also in 2D arrays and in that case is the row position. The row **of the array**... You can simple create an array as `arr = Range("A2:A100").value`. Iterations in arrays are much faster than in ranges... – FaneDuru Jan 18 '22 at 09:57
oh, ok so that "Application.Transpose(Evaluate("row(120:300)"))" has nothing to do with excel app and it's worksheet rows? – Eduards Jan 18 '22 at 09:58
1

@Eduards `Evaluate` has (in a way...). And `Transpose` transform a 2D rows array in a 1D array (no rows...). – FaneDuru Jan 18 '22 at 10:01
1

Gotcha, so it means rows inside the array. In which case your answer is perfect! – Eduards Jan 18 '22 at 10:02
There's a [follow-up question](https://stackoverflow.com/questions/70787194/vba-find-string-in-a-string-array) to this answer. – BigBen Jan 20 '22 at 13:59

VBA Array of integers from integer to integer without typing out every single one

2 Answers2