Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 4
There are 1 users currently browsing forums.
|
 |
08-22-2007, 01:44 PM
|
|
Valued Forum Contributor
|
|
Join Date: 03 Oct 2004
Posts: 102
|
|
|
Extracting 3 Arrays from 1 Array
Please Register to Remove these Ads
I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned as a Variant; its Lbound for each dimension is 0 and its UBound for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15 elements. (This array is imported into my VBA Code from Bloomberg.)
I do NOT want to paste this array into a worksheet; however, in design mode only, I am using the following to do exactly that (and it works):
Code:
ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0)
where UB1 is UBound for dimension1.
OK, I want to create 3 child arrays (one for each column) from the parent array. (My objective is to connect the individual elements for each column into one string, using a delimiter, and then paste that string into a specified cell in my worksheet.) This is my non-working code:
Code:
Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.
Do I need to use WorksheetFunction.Transpose?
Thanks for a reply.
Last edited by Chuckles123; 08-22-2007 at 01:59 PM.
|
08-22-2007, 02:44 PM
|
|
Valued Forum Contributor
|
|
Join Date: 16 Aug 2007
Location: Dublin. Ireland
Posts: 497
|
|
Hi there,
It's a bit difficult to diagnose with only the extract you've posted.
I can run the following code with no problems:
Code:
Sub TEST()
Const UB1 As Integer = 4
Dim Col_One As Variant, x As Integer
Dim vtData(4, 2) As Variant
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
End Sub
The only significant difference between your code & mine is that I've included a Dim statement for vtData - i.e. my code definitely "knows" what vtData is.
Are you sure that this variant has been declared in your code before the posted extract executes? Obviously it needs to be declared either within the routine itself or at the module or the Public level.
Hope this is helpful.
Regards,
Greg M
|
08-22-2007, 03:18 PM
|
|
Valued Forum Contributor
|
|
Join Date: 03 Oct 2004
Posts: 102
|
|
Thanks Greg, for your prompt reply.
I should have been more specific in my help request. When I said
Quote:
|
that is Dimensioned as a Variant
|
I should have used my Dim stmt, which is
Code:
Dim vtData As Variant
I tried using your
Code:
Dim vtData(4, 2) As Variant
Two problems: 1) the number of columns is fixed at 3, but the number of rows is a variable; and 2) I get an error at the following stmt
Code:
vtData = BlpBulk.BlpSubscribe(sSec, sFld)
(this is where the Bloomberg data is imported into my VBA Code) The Compile error: states "Can't assign to array".
Obiously, there is more going on here than I am aware.
Any more ideas?
|
08-22-2007, 03:55 PM
|
|
Valued Forum Contributor
|
|
Join Date: 03 Oct 2004
Posts: 102
|
|
|
Experiment
Since I am pulling in my array from Bloomberg, I have simulated the downloaded array; it has the same dimensions. My simulated array is:
Code:
Dim x As Integer, y As Integer
'POPULATE ARRAY
For y = 0 To 2 '<<< THIS BECOMES UB2
For x = 0 To 4 '<<< THIS BECOMES UB1
vtData(x, y) = (x * y) + x + y
Next x
Next y
Using the following stmt:
Code:
Dim vtData(0 To 4, 0 To 2) As Variant
my code works using the simulated array instead of the one downloaded from Bloomberg; however, this does not solve my problem because I will not know the value of UB1 until the array is downloaded from Bloomberg.
If I use my original Dim stmt for vtData, I get a "Type mismatch" error on the preceding vtData(x, y) = ... stmt for the simulated array.
Later
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
