I have some API based VBA macros working with no problem until I upgraded to 9.7.0. Did anything change in r9.7?
For macros to work with a COM based application, both applications need to be on the same bit size. In this case erwin and Excel, both should either be 64-bit or 32-bit. Support of 64-bit started with erwin 9.64.x, but the application is available in both, 32-bit as well 64-bit versions.
Additionally, in the Excel Macros (VBA), you will need to make sure that the correct SCAPI file is selected. In erwin that file is EAL.DLL and that needs to point to the correct folder. In the following screen shot, the file EAL.DLL is registered for an erwin 9.6.1 version installed in a folder "C:\ERwin_9601_Bin\".
You can do so in Excel, by going to the Macro and clicking edit to take you to the source code. Under Tools, click on Reference tab to take you to the menu as shown above. Make sure that the selected EAL.DLL is pointing to the correct folder. If you don't see the correct one, then it means that it will need to be registered using using Regsvr32.exe.
This should make the API part to work. The second part is make sure that the metadata being referred to in the code is correct. It should mostly be same, but some properties may have changed, e.g. if you have an API from olden times, the tag Logical_Datatype has been changed to Logical_Data_Type. It is just an example to clarify to make sure that that the correct tags are used in the code. If you still have any further questions, please contact erwin technical support.