- Joined
- Sep 25, 2008
- Messages
- 3
- Reaction score
- 0
To all out there....
I've been trying to modify an old piece of code that solves systems of linear equations. This code works fine when running in it's original form, that is, for a fixed worksheet within a fixed workbook and with a variable number of equations. The problem is that I'm trying to modify this code so that it works as an User Defined Function (UDF) so that instead of having a variable to count the rows from a fixed starting cell in order to know the number of equations, and having the inconvinience of running the code from a specific file instead of accessing it from any Excel file, the code can be used just by selecting a range of cells from any sheet and from any workbook. I tried to do something like the following:
Function Gauss(Equations as Range)
Dim NewM ()
Dim MatSize as Integer
Dim i,j as Integer
MatSize = Equations.Rows.Count
Redim NewM (1 to MatSize, 1 to MatSize + 1) as Double
for i = 1 to MatSize
for j = 1 to MatSize + 1
NewM(i,j) = Equations(i,j)
Next j
Next i
Then the code performs a series of operations on the "NewM" variable to solve the system. Up to this point, the array has the desired values but then, when I try to display the results, by using the next lines of code, something quite unexpected happens.
With ActiveSheet
for i = 1 to MatSize
for j = 1 to MatSize + 1
The next line is where the error occurs
.Cells(MatSize + 10 + i,j) = NewM(i,j)
if a Watch is added for NewM, a message appears stating the the variable is "Out of context"
Next j
Next i
End With
Can someone please tell me where I'm wrong. Does it has something to do with that once a Range is set or used, the indexes for any other reference are changed, or something like that?
THANKS A LOT....
I've been trying to modify an old piece of code that solves systems of linear equations. This code works fine when running in it's original form, that is, for a fixed worksheet within a fixed workbook and with a variable number of equations. The problem is that I'm trying to modify this code so that it works as an User Defined Function (UDF) so that instead of having a variable to count the rows from a fixed starting cell in order to know the number of equations, and having the inconvinience of running the code from a specific file instead of accessing it from any Excel file, the code can be used just by selecting a range of cells from any sheet and from any workbook. I tried to do something like the following:
Function Gauss(Equations as Range)
Dim NewM ()
Dim MatSize as Integer
Dim i,j as Integer
MatSize = Equations.Rows.Count
Redim NewM (1 to MatSize, 1 to MatSize + 1) as Double
for i = 1 to MatSize
for j = 1 to MatSize + 1
NewM(i,j) = Equations(i,j)
Next j
Next i
Then the code performs a series of operations on the "NewM" variable to solve the system. Up to this point, the array has the desired values but then, when I try to display the results, by using the next lines of code, something quite unexpected happens.
With ActiveSheet
for i = 1 to MatSize
for j = 1 to MatSize + 1
The next line is where the error occurs
.Cells(MatSize + 10 + i,j) = NewM(i,j)
if a Watch is added for NewM, a message appears stating the the variable is "Out of context"
Next j
Next i
End With
Can someone please tell me where I'm wrong. Does it has something to do with that once a Range is set or used, the indexes for any other reference are changed, or something like that?
THANKS A LOT....