Sleeper: Create VBA Function from VBA Macro - Inverse of Matrix in Excel VBA

[vinhp2013]

I have a file: Gauss Jordan for Inverse of Matrix 2.xlsm with a matrix in sheet1. I had a macro (GaussJordan) to replace this matrix to its inverse matrix.
My problem here is to create VBA Function to compute inverse of matrix that bases on Macro code.
Please help me.

Vinh

[EirikDaude]

What is it you expect the function to return? Assuming the Sub in your worksheet works the only thing you need to do to switch it to a function is to replace "sub" with "function" :P

[SamT]

I'm not sure that a function can handle this last line of the sub

Cells(m + 2, 1).Resize(m, m) = a

[Bob Phillips]

Is your matrix meant to just be A1:J10, or does it need to go all the way down to row 1000 (it will take forever!)?

[Paul_Hossler]

Not promising the math is correct, but the function can return a variant (array) if you array enter the formula --

Select the appropriate block of cells, enter the formula (without the braces), and then control+shift+enter -- that will add the array formual braces

screen shot and then the sample workbook

Option Explicit
Option Base 1
Function GaussJordan_func(Data As Range) As Variant
    Dim a As Variant, c#(), x#, y#
    Dim m&, u#, i&, j&, rv&()
    Dim q&, w&
    a = Data.Value  '   ---------------------------
    
    m = UBound(a, 1)
    
    ReDim c(1 To m, 1 To m)
    ReDim rv(1 To m, 1 To 2)
    
    For i = 1 To m
        c(i, i) = 1
    Next i
        
    For q = 1 To m
        u = 10 ^ 15
        For i = 1 To m
            If rv(i, 1) = 0 Then
                If a(i, q) <> 0 Then
                    If (Log(a(i, q) ^ 2)) ^ 2 < u Then
                        u = (Log(a(i, q) ^ 2)) ^ 2
                        w = i
                    End If
                End If
            End If
        Next i
        rv(w, 1) = w
        rv(q, 2) = w
        x = a(w, q)
        
        For j = 1 To m
            a(w, j) = a(w, j) / x
            c(w, j) = c(w, j) / x
        Next j
            
        For i = 1 To m
            If rv(i, 1) = 0 Then
                y = a(i, q)
                For j = 1 To m
                    a(i, j) = a(i, j) - y * a(w, j)
                    c(i, j) = c(i, j) - y * c(w, j)
                Next j
            End If
        Next i
    Next q
            
    'BACK SOLUTION
    For q = m To 2 Step -1
        For w = q - 1 To 1 Step -1
            x = a(rv(w, 2), q)
            a(rv(w, 2), q) = a(rv(w, 2), q) - x * a(rv(q, 2), q)
            For j = 1 To m
                c(rv(w, 2), j) = c(rv(w, 2), j) - x * c(rv(q, 2), j)
            Next j
        Next w
    Next q
        
    For q = 1 To m
        For j = 1 To m
            a(q, j) = c(rv(q, 2), j)
        Next j
    Next q
   
   GaussJordan_func = a     '---------------------
End Function

BTW, more descriptive variable names would have been helpful. This reminds me of FORTRAN

Paul

[Paul_Hossler]

and the xlsm if you want to play with it

My math is fuzzy, so I deleted a big chunk of your data, but the technique would probably work (slowly perhaps)

Paul

[vinhp2013]

Thank you very much, Paul.
Dim statement x#, c#() in this files, I don't understand because of my beginning in VBA. Can you explain for me?
By the way, are you good at C#, C++ or any languages? Especially programming for finance and investment. If any, we can learn with together because I am ready for studying C# or C++ for Quantitative Finance.
Thank You.

[Paul_Hossler]

The "#" is a type declaration character for a 'Double' 4 byte number:

Double data type -- A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic.

There's a whole bunch of them, but I always spell them out:

Dim x as Double, c() as Double

The only thing I can do in C# or C++ is spell them correctly most of the time

However for any VBA or Excel question, post them here and I'm sure you'll get a good answer

Paul