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
– Ike, Commented Jan 18, 2022 at 9: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
– Eduards, Commented Jan 18, 2022 at 9: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
– freeflow, Commented Jan 18, 2022 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
– freeflow, Commented Jan 18, 2022 at 10:41
I do realise that, used that just as the means of explaining the logic in somewhat of a code form — Eduards
– Eduards, Commented Jan 18, 2022 at 10:55

FaneDuru · Accepted Answer · 2022-01-18 09:34:35Z

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, 2022 at 9:34

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

FaneDuru Over a year ago

@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...

Eduards Over a year ago

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

FaneDuru Over a year ago

@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 Over a year ago

@Eduards Evaluate has (in a way...). And Transpose transform a 2D rows array in a 1D array (no rows...).

Eduards Over a year ago

Gotcha, so it means rows inside the array. In which case your answer is perfect!

|

Ike · Accepted Answer · 2022-01-18 09:22:23Z

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, 2022 at 9:22

answered Jan 18, 2022 at 9:14

Ike

13.4k6 gold badges20 silver badges38 bronze badges

9 Comments

Eduards Over a year ago

I would prefer something waaaaaaaay shorter because the task I am describing feels literlally like 1-2 lines of code

Ike Over a year ago

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 Over a year ago

added a one liner version :-)

Eduards Over a year ago

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

Ike Over a year ago

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

|

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related