Array built from array and declaration trouble

[SBF12345]

I would like to populate the array TempSample using PriceData. PriceData is currently defined using a worksheet table. How can I convert the current PriceData variant into something that will behave like a traditional array? If I ReDim PriceData(1 to A, 1 to 3) as Variant, where A is equal to UBound(PriceData,1) I receive an error down the code "unable to get the correl property of the worksheetfunction class". How can I define the first array starting from a table on a worksheet?

Dim PriceData As Variant:           PriceData = Worksheets(DataSht).Range(samplerange)
For rr = 1 To NumObs
        
        TempSample(rr, 1) = PriceData(ii + rr, 1) 
        TempSample(rr, 2) = PriceData(ii + rr, 2) 
        TempSample(rr, 3) = PriceData(ii + rr, 3)
    
    Next rr

 

[Kyle123]

Unless I'm misunderstanfing, simply:

Dim PriceData As Variant
PriceData = Worksheets(DataSht).Range(samplerange).Value

 

[Fluff]

Or is this what you mean

Dim PriceData As Variant, TempSample As Variant
PriceData = Worksheets(DataSht).Range(samplerange).Value2
ReDim TempSample(1 To UBound(PriceData), 1 To 3)

 

[SBF12345]

So the ".value" is the critical component in defining an array variant from a worksheet

In my add watch window I am looking at a bunch of empty values for ArrTemp(gg). Why would the below structure not work to load the array ArrTemp given the code above it? The ArrX array seems to load correctly.

For rr = 1 To NumObs        
        TempSample(rr, 1) = PriceData(ii + rr, 1) 
        TempSample(rr, 2) = PriceData(ii + rr, 2) 
        TempSample(rr, 3) = PriceData(ii + rr, 3) 
    
    Next rr
 For gg = 1 To NumObs    
    ArrX(gg) = Application.WorksheetFunction.Index(X, gg, 2)
    ArrTemp(gg) = Application.WorksheetFunction.Index(TempSample, gg, 2)
    ArrX1(gg) = Application.WorksheetFunction.Index(X, gg, 3)
    ArrTemp1(gg) = Application.WorksheetFunction.Index(TempSample, gg, 3)
    
    Next gg

 

[Fluff]

Assuming x is an array try

   For gg = 1 To NumObs
      ArrX(gg) = x(gg, 2)
      ArrTemp(gg) = TempSample(gg, 2)
      ArrX1(gg) = x(gg, 3)
      ArrTemp1(gg) = TempSample(gg, 3)
   Next gg

 

[SBF12345]

Im watching variables ArrX and ArrTemp. ArrTemp is not loading. The watch window gives a value of "subscript out of range".

When I redim variants PriceData and TempSample I have:

ReDim TempSample(1 To NumObs, 1 To 3)
ReDim PriceData(1 To A, 1 To 3) As Variant

A is the upperbound on Array PriceData and NumObs is the UpperBound on Array X. These two arrays exist with different numbers of observations. Is there an approach I can use to load the TempSample array's with the PriceData Array despite the difference in number of observations (likely avoiding the "subscript out of range error")? In other words I want to extract subsets of the array PriceData and load them into TempSample.

 TempSample(rr, 1) = Application.WorksheetFunction.Index(PriceData, ii + rr, 1) 'Dimension 1 TempSample - Date of Temp Sample        TempSample(rr, 2) = Application.WorksheetFunction.Index(PriceData, ii + rr, 2) 'Dimension 2 TempSample - price of Temp Sample
        TempSample(rr, 3) = Application.WorksheetFunction.Index(PriceData, ii + rr, 3)

 

[Fluff]

Firstly there is no need to use Index, it just slows everything down, you can do it like I showed in post#5.
Secondly I have no idea what you are trying to do and just seeing a few lines of code doesn't code.

 

[SBF12345]

The error is occurring on the line below:

DistanceMeasure = (1 - Application.WorksheetFunction.Correl(ArrX, ArrTemp)) * 100

I am fairly sure the error is occuring because the array "ArrTemp" is attempting to load using subscript values that are out of range. When I try to load the TempSample Array using the PriceData Array the elements of the two arrays are of different index values . TempSample(1 to NumObs,1 to 3) whereas PriceData(1 to A, 1 to 3), the NumObs and A variables are different index values creating a problem when TempSample tries to load 1 to NumObs and is met with PriceData which tries to load from values "ii +rr". "ii+rr" is a greater value than "rr" and is out of the range of the array TempSample. Even though the total number of values loaded is intended to be 1 to NumObs it looks like it doesn't want to load because the starting index value is greater than NumObs. In other words I need to grab a subset of the array, but am limited by the difference in index values for the two arrays. The bulk of the script is lowest on the post.

TempSample(rr, 1) = Application.WorksheetFunction.Index(PriceData,ii + rr, 1)            TempSample(rr, 2) = Application.WorksheetFunction.Index(PriceData,ii + rr, 2)
            TempSample(rr, 3) = Application.WorksheetFunction.Index(PriceData, ii + rr, 3)

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'X  define source data for total sample array   X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim DataSht As String:              DataSht = ActiveSheet.Range("C4").value
Dim samplerange As String:          samplerange = ActiveSheet.Range("C5").value
Dim PriceData As Variant:           PriceData = Worksheets(DataSht).Range(samplerange).value


'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  define source data for target sample array  X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim DataRange As String:            DataRange = ActiveSheet.Range("C6").value
Dim X As Variant:                   X = Worksheets(DataSht).Range(DataRange).value


Dim A As Integer:                   A = UBound(PriceData, 1)     ' A is the population size and contains full set of test sets
Dim NumObs As Integer:              NumObs = UBound(X, 1)        ' NumObs is the sample size


Dim TempSample() As Variant:        ReDim TempSample(1 To NumObs, 1 To 3)
Dim ArrX() As Variant:              ReDim ArrX(1 To NumObs)
Dim ArrX1() As Variant:             ReDim ArrX1(1 To NumObs)
Dim ArrTemp() As Variant:           ReDim ArrTemp(1 To NumObs)
Dim ArrTemp1() As Variant:          ReDim ArrTemp1(1 To NumObs)
Dim DistanceMeasure As Integer
Dim DistanceMeasure1 As Integer
ReDim PriceData(1 To A, 1 To 3) As Variant


Dim ii As Integer:              ii = 1  'total sample length
Dim rr As Integer:              rr = 0
Dim jj As Integer:              jj = 5
Dim gg As Integer:              gg = 1


For ii = NumObs To A
    
        For rr = 1 To NumObs
        
            TempSample(rr, 1) = Application.WorksheetFunction.Index(PriceData,ii + rr, 1)
            TempSample(rr, 2) = Application.WorksheetFunction.Index(PriceData,ii + rr, 2)
            TempSample(rr, 3) = Application.WorksheetFunction.Index(PriceData, ii + rr, 3)
    
        Next rr
    
    For gg = 1 To NumObs
    
        ArrX(gg) = X(gg, 2)
        ArrTemp(gg) = TempSample(gg, 2)
        ArrX1(gg) = X(gg, 3)
        ArrTemp1(gg) = TempSample(gg, 3)
    
    Next gg
DistanceMeasure = (1 - Application.WorksheetFunction.Correl(ArrX, ArrTemp)) * 100

 

[Fluff]

What happens if you remove this line

ReDim PriceData(1 To A, 1 To 3) As Variant

 

[SBF12345]

That seems to have worked. In the add watch window values are not specified for ii but the arrays load and the results seem to check out. I did some long hand tests and it seems to be flowing...Thanks!