The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
VBA in excel: Can't seem to make arrays work.
Diomedes240z
Registered User regular
Registered User regular
I'm a bit of a VBA n00b... my experience with programming is just BASIC and MATLAB. So obviously, I'm struggling with this. I'm making a VBA function that takes a group of cells as an array and performs a function on them lots of times, incrementing one element of the array each time.
The input to the function is 3, four column arrays. The output is a 2x3 array. My program does not seem to work, and I can't figure out why. I know that the function it calls works fine. I'm pretty sure it's not working because of the way I assign elements into the arrays, as I can make the program skip every line except for:
Static3(1, 4) = Static3(1, 4) + i
and make it output Static3(1, 4), and it still doesn't work. Can anybody help me here? Clearly there must be something wrong with my syntax.
Full code below:
Function ITSOLVER(Static1 As Variant, Static2 As Variant, Static3 As Variant, Changing1 As Variant, Changing2 As Variant) As Variant
'Operates IPSSS for given values, incrementing one matrix element for all integers between changing1 and changing2
'IPSSS outputs a 2X3 matrix
' Uses the following UDF's: IPSSS, IPCC, IPSS, Rotate
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ITmatrix As Variant
Dim IP As Variant
'double the number of integers because we're slotting in a 2 row matrix for each integer
Changing2 = Changing2 * 2
For i = Changing1 To Changing2
'increment one of the values slightly (this is the whole point of the program)
Static3(1, 4) = Static3(1, 4) + i
'fill the target matrix with delicious values.
For j = 1 To 3
For k = 1 To 2
IP = IPSSS(Static1, Static2, Static3)
ITmatrix(i + (k - 1), j) = IP(k, j)
Next k
Next j
Next i
'output something
ITSOLVER = ITmatrix
End Function
Apologies for my poor commenting on my code. I barely understand it well enough to write it
The input to the function is 3, four column arrays. The output is a 2x3 array. My program does not seem to work, and I can't figure out why. I know that the function it calls works fine. I'm pretty sure it's not working because of the way I assign elements into the arrays, as I can make the program skip every line except for:
Static3(1, 4) = Static3(1, 4) + i
and make it output Static3(1, 4), and it still doesn't work. Can anybody help me here? Clearly there must be something wrong with my syntax.
Full code below:
Function ITSOLVER(Static1 As Variant, Static2 As Variant, Static3 As Variant, Changing1 As Variant, Changing2 As Variant) As Variant
'Operates IPSSS for given values, incrementing one matrix element for all integers between changing1 and changing2
'IPSSS outputs a 2X3 matrix
' Uses the following UDF's: IPSSS, IPCC, IPSS, Rotate
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ITmatrix As Variant
Dim IP As Variant
'double the number of integers because we're slotting in a 2 row matrix for each integer
Changing2 = Changing2 * 2
For i = Changing1 To Changing2
'increment one of the values slightly (this is the whole point of the program)
Static3(1, 4) = Static3(1, 4) + i
'fill the target matrix with delicious values.
For j = 1 To 3
For k = 1 To 2
IP = IPSSS(Static1, Static2, Static3)
ITmatrix(i + (k - 1), j) = IP(k, j)
Next k
Next j
Next i
'output something
ITSOLVER = ITmatrix
End Function
Apologies for my poor commenting on my code. I barely understand it well enough to write it
Diomedes240z on
0
Posts
You could test it pretty quickly by just manually defining the whole static1, static2, static3 arrays and changing1 and 2 manually in the function (and get rid of the function call part) and see if the syntax works
How many rows are the statics? Let's say they're two, you'd be all dim static1(2,4) then say static1(1,1)=whatever, static1(1,2)=whatever etc.