C++ – Adding C++ function to VBA/Excel

After adding a c# library to VBA I have decided to take a look how to do a similar trick with C++.

C++ is a bit fancier in a way. And it works somehow else. So, let’s start. Imagine that we want to calculate the sum of all the numbers between 0 and n. The easiest way to do it (without thinking) is to take all the numbers and to sum them up. In C++ this formula would look like this:

int __stdcall SimpleSlowMath(int & x)
{
	int result = 0;
	for (int a = 0; a <= x; a++) {
		result += a;
	}
	return result;
}

Thus, in order to export this “beauty” to Excel with Visual Studio, we should do the following:

First create a new DLL project (Win32 Project) with VS.

Then select a DLL project and then check “Empty project”

Once in the empty project we need 2 files – a *.cpp file and a *.def file.

In the *.def file we write the following:

LIBRARY "VitoshAcademy"
EXPORTS
SimpleSlowMath

Before compiling, we go to Properties of the project>Linker>Input>Module Definition File and we write the name of the def file.

Compile the project with F6. And than go to VBA and have fun:

Declare Function SimpleSlowMath Lib "C:\Documents\Visual Studio 2015\Projects\SSMath\Debug\SSMath.dll" (ByRef x As Long) As Long
                             
Sub TestMe()
    
    Dim n As Long: n = 321
    
    Debug.Print SimpleSlowMath(n)
    Debug.Print SimpleSlowMathVBA(n)
    Debug.Print SimpleQuickMathVBA(n)

End Sub

Public Function SimpleSlowMathVBA(x As Long) As Long

    Dim result  As Long: result = 0
    Dim counter As Long
    
    For counter = 0 To x Step 1
        result = result + counter
    Next counter
    
    SimpleSlowMathVBA = result

End Function

Public Function SimpleQuickMathVBA(x As Long) As Long

    SimpleQuickMathVBA = (x * (x + 1)) / 2

End Function

The TestMe Sub routine now works! Taking the formula from the SSMath.dll, as we have written it in C++. Super 🙂

C++ – Adding C++ function to VBA/Excel

Related posts: