If I understand you correctly you want to create a COM object using C++ that can be instantiated/used by Office VBA.
You can use ATL to create an in-process (DLL) server. First create a new ATL project - creating-an-atl-project. Make sure it is a Dynamic Link Library.
After the project is created you can use the built-in wizard to add a Simple Object adding-an-atl-simple-object. This will add the infrastructure to your project for a COM object. When using the wizard select Single Threaded Apartment and Dual interface.
At this point the project contains the code for a C++ class that represents the COM object. This bare-bones class and the interface that it implements has automatic support for IUnknown/IDispatch but it is now up to you to add the methods and properties of interest.
For adding methods and properties, see adding-a-method-visual-cpp and adding-a-property-visual-cpp. The ATL wizards will take care of infrastructure like updating the IDL file, adding declarations to the header file and empty definitions to the cpp file. You must provide the actual code to be executed.
Unfortunately, Microsoft made substantial changes to the ATL wizards in Visual Studio versions after VS2015. These changes were not well done and MS asserted that the problems were finally corrected in VS2019 and were then eventually back-ported to VS2017. See the-big-atl-wizard-misery.html You may find discrepancies between the actual functioning of the wizards in VS2019 and the MS documentation.