Code like this will result in error in AutoCAD Architecture 2010 64-bit:
Set SchedApp = New AecScheduleApplication
Or this in AutoCAD 2010 64-bit:
Set SSM = New AcSmSheetSetMgr
One method that seems to work is to move the code from within a sub to the declaration like this:
Private SchedApp As New AecScheduleApplication
Another method is to replace it using GetInterfaceObject like this:
Set SchedApp = ThisDrawing.Application.GetInterfaceObject("AecX.AecScheduleApplication.6.0")
The reason is that VBA runs as an out-of-process client in 64-bit applications.
Unfortunately this also means that Sheet Set Manager API cannot be used in 64-bit applications because it is a COM server that cannot be called from another process as opposed to an ActiveX server.
Another 64-bit related problem is in AutoCAD Architecture 2009/2010 and AutoCAD MEP 2009/2010. In Tools>References the AutoCAD tlb shows up but not for the verticals. The same problem is also in .NET if you look at the COM tab when adding references.
The solution for this is a fix in the registry that look like this:
@="C:\\Program Files\\Common Files\\Autodesk Shared\\AecXBase60.tlb"
It needs to be done for each tlb you want to use. There is a win64 key but the win32 key is missing so it needs to be added.
Here is also a Microsoft KB describing similar problems with Office.