Trying to get VBA to communicate with C++ is like an English-speaking American trying to order dinner from a Chinese-speaking restaurant owner.
There is a language barrier in both cases. To tackle this problem, we need to start by identifying some common ground.
The solution is the same for both: pointers.
Both VBA and C++ understand the concept of pointers to memory locations. Likewise, both the American customer and Chinese restaurateur understand the concept of using one's pointer finger to show what one wants.
Found in Translation
To overcome the language barrier, the restaurant prints menus with choices shown in both English and Chinese.
The customer points to the English words that describe what he wants to order. The owner reads the Chinese characters printed alongside the English words. He prepares the food and sells it to the customer. Despite the two parties speaking different languages, the English/Chinese menu allows them to communicate.
The same concept applies in COM.
Translating Between VBA and C++
COM plays the role of the English/Chinese menu.
Rather than VBA and C++ talking to each other directly, VBA simply points to a location in memory where the relevant C++ functionality resides.
COM provides C++ with instructions for exactly how to organize its executable code when it gets loaded into memory. Then, COM tells VBA where to point to reach the executable code that C++ provides.
There's a bit more to it than that, but that is the basic overview.