Excel C Dll example, Array Formula, VBA matrix multiply interface, 2d array, and width height, rows and cols, buffer size in and out

Readme.md

Desired output

... 2 Ranges go in ... 1 Array Formula will spill out...

Take two ranges in excel, convert them to 1d c arrays ready to go into a c function call.

Pass in the buffer size as the width and height of the output array as allocated in VBA.

Return the buffer size modified by c to VBA.

Return a 2d buffer populated by C to VBA.

Convert it to a Array Formula.

Test

Built for x64 excel

Post the VBA in a Module in Excel.

Create two input ranges in excel, and pass it into the vba function. Press Ctrl+Shift+Enter on older versions of excel to force a spill for the array formula output.

=my_mmult_func(B15:D16, F15:G17)

If it detects the Array Formula correctly there should be some curly brackets that surround the formula.

Or from the Visual Basic editor on Excel, run a test function like this

Sub test_twod_array_func()
    Range("L7").FormulaArray = my_mmult_func(Range("B15:D16"), Range("F15:G17"))
End Sub

Note the print statments in c are basically useless in VBA.

Helpful Links

https://renenyffenegger.ch/notes/development/languages/VBA/Calling-DLLs/byRef-byVal

http://www.vbforums.com/showthread.php?495753-Passing-2D-Array-from-VBA-to-C

https://www.exceltip.com/custom-functions-in-vba/create-vba-function-to-return-array.html

Other advanced topics

https://github.com/keithalewis/xll12

https://github.com/VBA-tools/VBA-JSON

https://www.codeproject.com/Articles/17733/A-C-DLL-for-Excel-that-uses-Arrays-and-Ranges

my_func_vba.c

__stdcall long MY_DLL my_mmult(double * _matrix_a_in,  long   a_rows,     long   a_cols,
                                      double * _matrix_b_in,  long   b_rows,     long   b_cols,
                                      double * _matrix_c_out, long * c_rows_ptr, long * c_cols_ptr){
    // Check that the matricies share their inner dimension
    if(a_cols != b_rows){
        printf("Matrix dimensions are incompatible. "
               "\n  a_cols != b_rows"
               "\n  %ld != %ld", a_cols, b_rows);
        return __LINE__;
    }

    // Check to make sure the buffersize is big enough
    long c_cols = *c_cols_ptr;
    long c_rows = *c_rows_ptr;
    if(c_cols*c_rows < a_rows*b_cols){
        printf("Output matrix is too small to store the data. "
               "\n  a_rows x b_cols < c_cols x c_rows "
               "\n  %ld x %ld < %ld x %ld "
               "\n %ld < %ld ", a_rows, b_cols, c_cols, c_rows, a_rows*b_cols, c_cols*c_rows);
        return __LINE__;
    }

    printf("matrix_a_in:\n");
    for(int r = 0; r < a_rows; r++){
        for(int c = 0; c < a_cols; c++)
            printf("\t%g", _matrix_a_in[r*a_cols + c]);
        printf("\n");
    }

    printf("\n\n");
    printf("matrix_b_in:\n");
    for(int r = 0; r < b_rows; r++){
        for(int c = 0; c < b_cols; c++)
            printf("\t%g", _matrix_b_in[r*b_cols + c]);
        printf("\n");
    }
    printf("\n\n");

    // resize c dimensions if needed
    if(c_cols != b_cols)
        *c_cols_ptr = b_cols;
    if(c_rows != a_rows)
        *c_rows_ptr = a_rows;
    c_cols = *c_cols_ptr;
    c_rows = *c_rows_ptr;

    printf("matrix_c_out:\n");
    for(int r = 0; r < c_rows; r++){
        for(int c = 0; c < c_cols; c++){
            // TODO, Do real math here or pass to another library
            _matrix_c_out[r*c_cols + c] = r*100 + c;
            printf("\t%g", _matrix_c_out[r*c_cols + c]);
        }
        printf("\n");
    }

    return __LINE__;
}

//#include <windows.h>
int _stdcall myarray( double* pin, double* pout, int sz )
{
     for ( int i = 0; i < sz; i++ )
     {
        pout[i] = pin[i] * 100;
     }
     return 0;
}

my_func_vba.def

LIBRARY "my-func-vba"
EXPORTS
my_mmult
myarray

my_func_vba.h

#ifndef MY_DLL_H
#define MY_DLL_H

#ifdef __cplusplus
extern "C" {
#endif

#ifndef WIN32
#define __stdcall
#endif

#if defined(_WIN32)
    //  Microsoft
    #ifdef BUILDING_MY_DLL
        #define MY_DLL __declspec(dllexport)
    #else
        #define MY_DLL __declspec(dllimport)
    #endif
#elif defined(__GNUC__)
    //  GCC
    #define MY_DLL __attribute__((visibility("default")))
#endif

__stdcall long MY_DLL my_mmult(double * _matrix_a_in,  long a_cols, long a_rows,
                                      double * _matrix_b_in,  long b_cols, long b_rows,
                                      double * _matrix_c_out, long * c_cols_ptr, long * c_rows_ptr);

int _stdcall myarray( double* pin, double* pout, int sz );

#ifdef __cplusplus
}
#endif

#endif  // MY_DLL_H

twod_array_func.vba

Declare PtrSafe Function my_mmult Lib "C:/path/to/my_func_vba.dll" _
(matrix_a_in#, ByVal a_rows&, ByVal a_cols&, matrix_b_in#, ByVal b_rows&, ByVal b_cols&, matrix_c_out#, ByRef c_rows As Long, ByRef c_cols As Long) As LongPtr

Declare PtrSafe Function myarray& Lib "C:/path/to/my_func_vba.dll" (pin#, pout#, ByVal sz&)

Sub trydll()
 Dim sz&, pin#(), pout#()
 sz = 10
 ReDim pin(sz), pout(sz)
 For i = 1 To sz
 pin(i) = i
 Next i
 
 Y = myarray(pin(1), pout(1), sz)
 MsgBox pout(3)
End Sub

Sub trydll2()
 Dim sz&, ain#(), bin#(), cout#()
 sz = 6
 ReDim ain(sz), bin(sz), cout(sz)
 For i = 1 To sz
 ain(i) = i
 bin(i) = i
 cout(i) = i
 Next i
 a_cols = 3
 a_rows = 2
 b_cols = 2
 b_rows = 3
 c_cols = 2
 c_rows = 2
 Stop
 ' Pass in the first element of each array
 Y = my_mmult(ain(1), a_cols, a_rows, bin(1), b_cols, b_rows, cout(1), c_cols, c_rows)
 Stop
 MsgBox cout(1)
 MsgBox cout(2)
 MsgBox cout(3)
 MsgBox cout(4)
End Sub

Public Function my_mmult_func(range_a As Range, range_b As Range) As Double()
    'define array
    Dim matrix_a As Variant
    Dim matrix_b As Variant
    Dim matrix_c As Variant
    matrix_a = range_a
    matrix_b = range_b
    ReDim matrix_c(0 To range_a.Rows.Count, 0 To range_b.Columns.Count)
    
    For i = 0 To UBound(matrix_c, 1)
        For j = 0 To UBound(matrix_c, 2)
            matrix_c(i, j) = 1#
        Next j
    Next i
    
    a_rows = range_a.Rows.Count
    a_cols = range_a.Columns.Count
    b_rows = range_b.Rows.Count
    b_cols = range_b.Columns.Count
    Dim c_rows&, c_cols&
    c_rows = range_a.Rows.Count + 2
    c_cols = range_b.Columns.Count + 2
    
    Dim sz&, a#(), b#(), cout#(), array_retval#()
    ReDim a(a_rows * a_cols)
    ReDim b(b_rows * b_cols)
    ReDim cout(c_rows * c_cols)
    
    Dim R, C As Integer
    For R = 1 To a_rows
       For C = 1 To a_cols
          a((R - 1) * a_cols + C) = matrix_a(R, C)
       Next
    Next
    For R = 1 To b_rows
       For C = 1 To b_cols
          b((R - 1) * b_cols + C) = matrix_b(R, C)
       Next
    Next
    For R = 1 To c_rows
       For C = 1 To c_cols
          cout((R - 1) * c_cols + C) = 1#  'matrix_c(R, C)
       Next
    Next
    
    Debug.Print "a"
    For Count = LBound(a) To UBound(a)
        Debug.Print a(Count)
    Next Count
    Debug.Print "b"
    For Count = LBound(b) To UBound(b)
        Debug.Print b(Count)
    Next Count
    Debug.Print "c"
    For Count = LBound(cout) To UBound(cout)
        Debug.Print cout(Count)
    Next Count
    Stop
    retval = my_mmult(a(1), a_rows, a_cols, b(1), b_rows, b_cols, cout(1), c_rows, c_cols)
    
    Debug.Print "cout"
    For Count = 1 To CInt(c_rows * c_cols)
        Debug.Print cout(Count)
    Next Count
    
    ReDim array_retval(c_rows, c_cols)
    For R = 1 To c_rows
        For C = 1 To c_cols
          array_retval(R - 1, C - 1) = cout((R - 1) * c_cols + C)
       Next
    Next
        
    Stop
    my_mmult_func = array_retval
End Function


Sub trydll3()
    Range("L7").FormulaArray = my_mmult_func(Range("B15:D16"), Range("F15:G17"))
End Sub