VBA arrays

[hokiebird]

Hello all, first time post

I'm trying to access only part of an array.

Suppose A in an array of integers with 10 cells...
Dim A(1 to 10) as integer

...some code to fill up A

Now lets say I want to call a subroutine with only cells 4-8, how do
do this?

In other codes we can do something like this...
Call SomeSub(A(4:8))
but i can't for the life of me figure out how to do this in VBA...
HELP

 

[Dave]

Create a new array A1:
For i = 4 to 8
j= j+1
A1(j) = A(i)
Next i

Then pass the new array to your sub:
Call SomeSub(A1)

remember to dim the variables and that if you don't use option base 1 that
arrays start at 0 not 1 in visual basic.

 

[Guest]

Another solution:

Sub xx()
' Just an array to use
a = Evaluate("{1,2,3,4,5,6,7,8,9,10}")
' Create another array from the given a
b = Evaluate("{" & a(5) & "," & a(6) & "," & a(7) & "," & a(8) & "}")
' call the function using the subset
Call myfn(b)
End Sub

 

[Jamie Collins]

I'm trying to access only part of an array.

Suppose A in an array of integers with 10 cells...
Dim A(1 to 10) as integer

..some code to fill up A

Now lets say I want to call a subroutine with only cells 4-8, how do I
do this?

Perhaps you'd prefer to use an in-memory object with more
functionality than an array e.g.

Sub Test()
Dim A As Object
Set A = CreateObject("ADODB.Recordset")
With A
.CursorLocation = 3
.Fields.Append "DataCol", 3
.Open

' code to fill up A
Dim i As Long
For i = 1 To 10
.AddNew "DataCol", i
Next

.Filter = "DataCol >= 4 AND DataCol <= 8"
MsgBox .GetString
End With
End Sub

If you require an array as output, use the GetRows method (use
Excel.Application.Transpose to correspond with Excel's row,column
format). Rather than use hard-coded values, use a SQL query to fetch
the data.

Jamie.

--