VBA arrays
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 -- hokiebir ----------------------------------------------------------------------- hokiebird's Profile: http://www.excelforum.com/member.php...fo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27699 |
VBA arrays
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. "hokiebird" wrote in message ... 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 I 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! -- hokiebird ------------------------------------------------------------------------ hokiebird's Profile: http://www.excelforum.com/member.php...o&userid=16308 View this thread: http://www.excelforum.com/showthread...hreadid=276996 |
VBA arrays
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 "hokiebird" wrote: 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 I 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! -- hokiebird ------------------------------------------------------------------------ hokiebird's Profile: http://www.excelforum.com/member.php...o&userid=16308 View this thread: http://www.excelforum.com/showthread...hreadid=276996 |
VBA arrays
hokiebird wrote ...
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. -- |
All times are GMT +1. The time now is 12:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com