(VBA) Code To Load An Array With Sequential Numbers.

[kelly mort]

Sub LoopThroughArray()
  'create array
  Dim strName() As Variant 
  strName() = Array(1, 2, 3, 4, 5, 6) 

Dim strFind as string 
strFind = 20

Dim i As Long
'loop through the array
   For i = LBound(strName, 1) To UBound(strName, 1)
       If InStr(strName(i), strFind) > 0 Then
          MsgBox "Number has been found!"
          Exit For
       End If
    Next i
End Sub

My goal is to be able to find a way to load 1 up to 100 or any upper limit without doing all the numbering manually.

strName() = Array(1, 2, 3, 4, 5, 6)

Please I need help.

Thanks

 

[Peter_SSs]

Do I need the Evaluate?

No. Is there an advantage in using Application instead?

They look pretty much identical to me.

seq = Application.Sequence(1, 5, 1, 1)
arr = Evaluate("sequence(1,5,1,1)")

 

[JenniferMurphy]

No. Is there an advantage in using Application instead?

They look pretty much identical to me.

seq = Application.Sequence(1, 5, 1, 1)
arr = Evaluate("sequence(1,5,1,1)")

I thought that Application had a bit of an advantage in that it could be partially "compiled", whereas Evalute had to be parsed at run time. No?

 

[Peter_SSs]

Not an area of expertise of mine though for the question you originally posed, I can't see there would be a significant difference.

 

[Georgiboy]

The Sequence method of the Application object in VBA is a built-in function that is optimized for performance by the VBA runtime environment. When you call the Sequence method, the VBA compiler generates machine code that directly implements the function, without having to interpret the formula at runtime.

This means that, compared to using the Evaluate method, calling the Sequence method can be faster and use less memory, especially when working with large datasets or in time-sensitive applications.

However, it's important to note that the performance difference between the two approaches is generally small and may not be noticeable in most cases. Additionally, the performance advantage of using the Sequence method may be outweighed by other factors, such as ease of use or compatibility with other applications or programming languages.

Overall, while the Sequence method in VBA does have some performance advantages, the choice between using it or the Evaluate method should be based on your specific requirements and context.

 

[JenniferMurphy]

Not an area of expertise of mine though for the question you originally posed, I can't see there would be a significant difference.

Yes, I agree that the performance diffrerence in this application is probably insignificant. I was just explaining my rationale.

 

[JenniferMurphy]

The Sequence method of the Application object in VBA is a built-in function that is optimized for performance by the VBA runtime environment. When you call the Sequence method, the VBA compiler generates machine code that directly implements the function, without having to interpret the formula at runtime.

This means that, compared to using the Evaluate method, calling the Sequence method can be faster and use less memory, especially when working with large datasets or in time-sensitive applications.

However, it's important to note that the performance difference between the two approaches is generally small and may not be noticeable in most cases. Additionally, the performance advantage of using the Sequence method may be outweighed by other factors, such as ease of use or compatibility with other applications or programming languages.

Overall, while the Sequence method in VBA does have some performance advantages, the choice between using it or the Evaluate method should be based on your specific requirements and context.

Good to know. Thanks.

Is that true of all Application.xyz functions?

 

[Georgiboy]

Is that true of all Application.xyz functions?

That is my understanding