Problem with trying to input values in a multidimensional array vba

[TheDynamo]

Hi I am having some issues trying to put values into an array. I have only been coding two weeks so i'm not sure if it's the actual creation that's the issue or inputting the variables. The debugger seems to go past the defining of the array and falls over when I try and input a value for the first dimension of the array.

I have added the spreadsheet as an attachment and the code I am looking at is in module 2 of that sheet. I have added some comments as suggested when joining the forum and I hope that helps.

Any ideas? The coding is probably very inefficient but I will try my best to change these with your guidance.

 

[SkipVought]

hi,

Please post your code HERE. Many of us cannot download from the internet due to company security restirctions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TheDynamo]

This is the part I'm having problems with. Sorry if the code is rubbish but have only just started VBA and am peicing bits together.

Dim DataArra() As Variant
ReDim DataArra(1 To j, 1 To MaxDateCount, 1 To 11, 0) As Variant

MonthCol = 1
DateRow = 2
DateCol = 2
TimeRow = 2
TimeCol = 3
FutRow = 2
FutCol = 5

'I'm trying to stage by stage input each data point then work down the tree.First put in the a, then thefirst b followed be c etc then loop to complete all the b's for that a and so on.

For a = 0 To j
'this is the loop to get the month code

If DataSheet.Cells(2, MonthCol) <> Empty Then
DataArra(a) = DataSheet.Cells(2, MonthCol)

'this will only put the dates available into the month code
For b = 0 To MaxDayCount

If DataSheet.Cells(DateRow, 2 + (MonthCol - 1)) <> Empty Then

DataArra(a, b) = DataSheet.Cells(DateRow, 2 + (MonthCol - 1))

'this will only put the hours of data in for the that specific date
For c = 0 To 11

DataArra(a, b, c) = DataSheet.Cells(TimeRow, 3 + (MonthCol - 1))
DataArra(a, b, c, 0) = DataSheet.Cells(TimeRow, 5 + (MonthCol - 1))

If DataSheet.Cells(TimeRow, 3 + (MonthCol - 2)) = DataSheet.Cells(TimeRow + 1, 3 + (MonthCol - 2)) Then

TimeRow = TimeRow + 1

Else
End If
Next c
TimeRow = TimeRow + 1
DateRow = TimeRow

Else
End If
Next b
MonthCol = MonthCol + 5
Else
End If
Next a


End Sub

 

[TheDynamo]

this is some additional comment that I had before what I posted


'I use the j and MaxDateCount I calculated earlier and I also know that the most amount of time points any day can have is
'11 becuase that is the data I have put into the data blocks in DataSimple sheet. Some will not have that many. The dates dimension
'varies for each j as some has less then others and I will not completely fill these. I feel that jagged array might be more effecient
'or if i keep resizing the array everytime I find a new date.

 

[SkipVought]

Please also post some sample data -- COPY 'n' PASTE a few rows.

Also please explain, in plain words, what you are attempting to accomplish.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TDynamo]

sorry problems logging in

Symbol Date Time Date Time Open Symbol Date Time Date Time Open Symbol Date Time Date Time Open
URH06 01/07/2003 08:00 01/07/2003 08:00 96.715 URM06 01/07/2003 15:00 01/07/2003 15:00 96.57 URU06 01/07/2003 15:00 01/07/2003 15:00 96.42
URH06 01/07/2003 09:00 01/07/2003 09:00 96.73 URM06 01/07/2003 16:00 01/07/2003 16:00 96.56 URU06 04/07/2003 08:00 04/07/2003 08:00 96.3
URH06 01/07/2003 13:00 01/07/2003 13:00 96.73 URM06 02/07/2003 14:00 02/07/2003 14:00 96.48 URU06 08/07/2003 08:00 08/07/2003 08:00 96.215
URH06 01/07/2003 14:00 01/07/2003 14:00 96.73 URM06 03/07/2003 08:00 03/07/2003 08:00 96.45 URU06 08/07/2003 09:00 08/07/2003 09:00 96.2
URH06 01/07/2003 15:00 01/07/2003 15:00 96.76 URM06 03/07/2003 13:00 03/07/2003 13:00 96.45 URU06 08/07/2003 10:00 08/07/2003 10:00 96.215
URH06 01/07/2003 16:00 01/07/2003 16:00 96.735 URM06 03/07/2003 14:00 03/07/2003 14:00 96.495 URU06 08/07/2003 14:00 08/07/2003 14:00 96.28
URH06 02/07/2003 09:00 02/07/2003 09:00 96.67 URM06 03/07/2003 15:00 03/07/2003 15:00 96.46 URU06 08/07/2003 16:00 08/07/2003 16:00 96.28
URH06 02/07/2003 10:00 02/07/2003 10:00 96.65 URM06 03/07/2003 16:00 03/07/2003 16:00 96.45 URU06 09/07/2003 14:00 09/07/2003 14:00 96.305
URH06 02/07/2003 11:00 02/07/2003 11:00 96.645 URM06 04/07/2003 09:00 04/07/2003 09:00 96.465 URU06 11/07/2003 13:00 11/07/2003 13:00 96.325
URH06 02/07/2003 12:00 02/07/2003 12:00 96.645 URM06 04/07/2003 10:00 04/07/2003 10:00 96.465 URU06 14/07/2003 13:00 14/07/2003 13:00 96.39
URH06 02/07/2003 13:00 02/07/2003 13:00 96.615 URM06 04/07/2003 11:00 04/07/2003 11:00 96.485 URU06 14/07/2003 15:00 14/07/2003 15:00 96.405
URH06 02/07/2003 14:00 02/07/2003 14:00 96.64 URM06 04/07/2003 13:00 04/07/2003 13:00 96.475 URU06 14/07/2003 16:00 14/07/2003 16:00 96.41
URH06 02/07/2003 17:00 02/07/2003 17:00 96.665 URM06 04/07/2003 15:00 04/07/2003 15:00 96.475 URU06 15/07/2003 09:00 15/07/2003 09:00 96.355
URH06 03/07/2003 12:00 03/07/2003 12:00 96.6 URM06 07/07/2003 11:00 07/07/2003 11:00 96.43 URU06 15/07/2003 14:00 15/07/2003 14:00 96.315
URH06 03/07/2003 13:00 03/07/2003 13:00 96.605 URM06 07/07/2003 13:00 07/07/2003 13:00 96.435 URU06 16/07/2003 08:00 16/07/2003 08:00 96.085
URH06 03/07/2003 14:00 03/07/2003 14:00 96.66 URM06 07/07/2003 15:00 07/07/2003 15:00 96.44 URU06 16/07/2003 09:00 16/07/2003 09:00 96.12
URH06 03/07/2003 15:00 03/07/2003 15:00 96.605 URM06 07/07/2003 16:00 07/07/2003 16:00 96.45 URU06 16/07/2003 10:00 16/07/2003 10:00 96.125
URH06 03/07/2003 17:00 03/07/2003 17:00 96.61 URM06 08/07/2003 08:00 08/07/2003 08:00 96.38 URU06 16/07/2003 13:00 16/07/2003 13:00 96.125
URH06 04/07/2003 09:00 04/07/2003 09:00 96.635 URM06 08/07/2003 09:00 08/07/2003 09:00 96.37 URU06 16/07/2003 14:00 16/07/2003 14:00 96.145

what I want to do is create an array. So under each expiry symbol I have stored each date. within each date I would like to have all the available times. Each time will only have one input which is the futures price. This data block can be off varying size so I've written a few bits of code which counts how many symbols I have and the maximum amount of dates any date column would have.

hope this explains it

 

[SkipVought]

First of all, you would be much better off if ALL your data were in A:F rather than spread out in 3 groups of 6 columns.

Then WHY do you have Date & Time fields repeated?

Restructure your data as I suggested in 6 columns, assuming that you have Excel 2007 or greater, convert your table to a Structured Table via Insert > Tables > Table and NAME you Structured Table tTheDynamo

Paste this code into a module and run. View results in the IMMEDAITE window of the VB Editor.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops forgot the code

Sub test()
    Dim a, i As Integer, j As Integer
    
    a = [tTheDynamo[#Data]]
    
    For i = LBound(a, 1) To UBound(a, 1)
        For j = LBound(a, 2) To UBound(a, 2)
            Debug.Print a(i, j)
        Next
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

What is the purpose of using a array? I cannot imagine you can do anything with it easier than simply using the range object or another data structure.

 

[TheDynamo]

Skip thanks for the help it's appreciated.

The reason I have date/time column is becuase I was using that value in a previous bit of code to line up different the values from different month symbols on the same date and time. I could have just done all of that in the code rather then output it to a cell. But hey that was my first intro to vba so learned it wasn't the most effiecent.

The reason I wanted to put the data in an array is becuase I thought it would be easier to manage. This is the whole task that I am trying to achieve. Using the above data as a example first I would look at the first date and time available for URH06, then look to see if the same date and time is available for URM06 and then in URU06. Providing I have all the of the same date and time I would do a calculation and either output the value to a sheet. If the three corresponding dates and times aren't there I would then look for the next time of the same day and see if that was available for all three. I would just repeat that process till I got to the end or got all the date and times stamps that matched up for three.

Then I would repeat this with URM06 being the first month that I check to see if there is the same date and time in URU06 and URz06 which you can't see on here. Then repeat so see if I have the days and times and if so a calculation. I have 36 months symbols so as I keep repeating this loop aout 33 times by shifting one month symbol along I thought it would be better to input everything into an array and read through the date that way.

I can input the code of what I would pretty much be repeating if it would be simpler. It's also in the same spreadsheet I uploaded.

 

[MajP]

No way. A multi-dimensional array would be probably the worse way to do this. There are several ways to set this up, but I sure would not use arrays.

 

[TheDynamo]

I'm doing the basic version of what I'm trying to achieve already through this code. Here I specify the date I want to start and finish looking. Now I'm just going to look through all the days and not specify how many month symbols I am looking at. So it will either be 2 or 3.

I coded it up as below and thought and array to store the data and use later would be an improvement rather then outputting into a sheet and then working with the data.

'This goes through the columns of data to find the specified symbol for month one. If that is present it will look for month2 and so on until
'all month columns are found.

Do Until Month1 = DataSheet.Cells(2, ColCountM1)
ColCountM1 = ColCountM1 + 5
FindEndCol = DataSheet.Cells(2, ColCountM1)
Loop

If Month2 <> Empty Then
Do Until Month2 = DataSheet.Cells(2, ColCountM2)
ColCountM2 = ColCountM2 + 5
Loop

If Month3 <> Empty Then
Do Until Month3 = DataSheet.Cells(2, ColCountM3)
ColCountM3 = ColCountM3 + 5
Loop

If Month4 <> Empty Then
Do Until Month4 = DataSheet.Cells(2, ColCountM4)
ColCountM4 = ColCountM4 + 5
Loop
End If
End If
End If


'This will work down the column for each month to find the closest date point to the start date.

Do Until DataSheet.Cells(RowCountM1, ColCountM1 + 3) > StartTime
RowCountM1 = RowCountM1 + 1
CurrDate = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
Loop

If Month2 <> Empty Then
Do Until DataSheet.Cells(RowCountM2, ColCountM2 + 3) > StartTime
RowCountM2 = RowCountM2 + 1
CurrDate = DataSheet.Cells(RowCountM2, ColCountM2 + 3)
Loop

If Month3 <> Empty Then
Do Until DataSheet.Cells(RowCountM3, ColCountM3 + 3) > StartTime
RowCountM3 = RowCountM3 + 1
CurrDate = DataSheet.Cells(RowCountM3, ColCountM3 + 3)
Loop

If Month4 <> Empty Then
Do Until DataSheet.Cells(RowCountM4, ColCountM4 + 3) > StartTime
RowCountM4 = RowCountM4 + 1
CurrDate = DataSheet.Cells(RowCountM4, ColCountM4 + 3)
Loop

End If
End If
End If

'I'm looking to find how many data points I have between start and end times. So I use the cell which is the first cell from
'the start time to count from then loop till i get the end data point. Then I will know how many times to loop the following procedure
'because the first month symbol is the proxy for the rest.

EnDateCountM1 = RowCountM1

Do
EnDateCountM1 = EnDateCountM1 + 1
CurrDate = DataSheet.Cells(EnDateCountM1, ColCountM1 + 3)
Loop While DataSheet.Cells(EnDateCountM1, ColCountM1 + 3) < EndTime

i = 1 + EnDateCountM1 - RowCountM1

'Here I will use the first date in the range I'm looking at and try and find the corresponding one in each other month symbol.
'if it can't find it for that month then I just move and check for the next day. If it is present I do a little cal and store it.
For n = 1 To i

If Month1 <> Empty Then

StratPrice = 0
FutValueM1 = DataSheet.Cells(RowCountM1, ColCountM1 + 4)
ResultsSheet.Cells(1 + n, 1) = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
ResultsSheet.Cells(1 + n, 2) = FutValueM1
StratPrice = (InputSheet.Cells(7, 3) * FutValueM1)

If Month2 <> Empty Then

If DataSheet.Cells(RowCountM2, ColCountM2 + 3) < DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
Do Until DataSheet.Cells(RowCountM2, ColCountM2 + 3) > DataSheet.Cells(RowCountM1, ColCountM1 + 3)
RowCountM2 = RowCountM2 + 1
Loop
Else
End If

FutValueM2 = DataSheet.Cells(RowCountM2, ColCountM2 + 4)
If DataSheet.Cells(RowCountM2, ColCountM2 + 3) = DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
ResultsSheet.Cells(1 + n, 3) = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
ResultsSheet.Cells(1 + n, 4) = FutValueM2
RowCountM2 = RowCountM2 + 1
StratPrice = StratPrice + (InputSheet.Cells(7, 4) * FutValueM2)

If Month3 <> Empty Then

If DataSheet.Cells(RowCountM3, ColCountM3 + 3) < DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
Do Until DataSheet.Cells(RowCountM3, ColCountM3 + 3) > DataSheet.Cells(RowCountM1, ColCountM1 + 3)
RowCountM3 = RowCountM3 + 1
Loop
Else
End If

FutValueM3 = DataSheet.Cells(RowCountM3, ColCountM3 + 4)
If DataSheet.Cells(RowCountM3, ColCountM3 + 3) = DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
ResultsSheet.Cells(1 + n, 5) = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
ResultsSheet.Cells(1 + n, 6) = FutValueM3
RowCountM3 = RowCountM3 + 1
StratPrice = StratPrice + (InputSheet.Cells(7, 5) * FutValueM3)

If Month4 <> Empty Then

If DataSheet.Cells(RowCountM4, ColCountM4 + 3) < DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
Do Until DataSheet.Cells(RowCountM4, ColCountM4 + 3) > DataSheet.Cells(RowCountM1, ColCountM1 + 3)
RowCountM4 = RowCountM4 + 1
Loop
Else
End If

FutValueM4 = DataSheet.Cells(RowCountM4, ColCountM4 + 4)
If DataSheet.Cells(RowCountM4, ColCountM4 + 3) = DataSheet.Cells(RowCountM1, ColCountM1 + 3) Then
ResultsSheet.Cells(1 + n, 7) = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
ResultsSheet.Cells(1 + n, 8) = FutValueM4
RowCountM4 = RowCountM4 + 1
StratPrice = StratPrice + (InputSheet.Cells(7, 6) * FutValueM4)

End If
End If
End If
End If
End If
End If
ResultsSheet.Cells(1 + n, 10) = DataSheet.Cells(RowCountM1, ColCountM1 + 3)
ResultsSheet.Cells(1 + n, 11) = StratPrice
RowCountM1 = RowCountM1 + 1
End If
Next n

 

[TheDynamo]

Shall i just continue working through this methodology? Just thought there was a better way but this works.

 

[MajP]

For academic reasons you may choose to continue what you are doing, I am sure you can make this overly complicated code eventually work. However, if you want easy useable solutions I personally would quit.
1) you could import this into Access and then everything you have discussed so far would be trivial. Little if any code would be needed. These are some very simple queries.
2) You could simply use the range object to do all of this, assuming you put in a contigous range.
3) If you are really set with arrays then building a 2d array from a range is a single line of code as Skip showed. Use that to your advantage
Assume you have 10 non contigous ranges.
With 10 lines of code, build 10 2d arrays.
Get the sum of all dimension of the 10 arrays. (1 line of code)
Dimension a new 2d array (total records, columns) (1 line of code)
populate the new 2d array with all the values of each array. (about 10 lines of code)