0

This code is originally much longer and intends to use Savitsky-golay fitting to take derivative estimates. In order to take find the derivative matrix needed to fit against original data, I need to follow through a series of excel worksheet functions (namely: Golay_matrix=MMult(MInverse(MMult(Transpose(matrix), matrix)), Transpose(matrix))) I'm relatively new to excel vba and feel like I have exhausted most avaiable resouces trying to solve what appears to be a WorksheetFunction problem. I can code in a transpose function myself, but I would rather not try to code in a MInverse function.. since eventually I will need to use MInverse, MMult, etc., I figured it best if I figured out what was going wrong with the front portion of my code.

I would love any bit of your help!! Thanks!!

Function d_ar(ends As Integer, fit As Integer) As Double()
Dim lngt As Integer 'length of "huge" array
Dim i As Integer
Dim n As Integer
lngt = ends * 2 + 1

Dim huge() As Variant 'array to transpose

For i = 1 To fit + 1
    For n = 1 To lngt
        ReDim huge(1 To n, 1 To i)
        huge(n, i) = (-ends + n - 1) ^ (i - 1)
    Next n
Next i
Dim t_hg() As Variant 'transpose of "huge" matrix
ReDim t_hg(1 To i - 1, 1 To n - 1) 
t_hg = Application.WorksheetFunction.Transpose(huge)
Cells(1, 15).value = t_hg(1, 2) 'value of zero is returned, but should be -7
End Function
user3933289
  • 3
  • 3

1 Answers1

1

You are ReDiming your huge array in the for loop, and ReDim by default zeros out the array. If you want to preserve existing values, you will need to use ReDim Preserve huge(1 To n, 1 To i).

In addition, wouldn't it be sufficient to set the array size once before the for loops?

MP24
  • 3,110
  • 21
  • 23
  • Thank you so much. Yeah, I had previously declared the huge array outside of the for loop, but because I continued to have problems, I was worried perhaps I was misinterpreting the array size... as such, I made placed the redim within the for loop to make the array fit perfectly...and thus the advent of my problems. On top of this, I happened to be printing out the last cell in the huge matrix to confirm that the array wasn't empty.. and that just happened to be the only cell that was filled. Hah.. whoops. Thank you so much! – user3933289 Aug 12 '14 at 13:03