"Excel should be the only thing using it":
I found out the hard way that possibly the only way to accomplish this application restriction is hackish or cryptic. AFAIK: keep a hard-coded or external list of calling application white/blacklists referenced by your library, with a lookup performed on each library load. By nature, COM classes are exposed to the entire world... literally.
"whether there is a way to make both direct calls in formulas and calls via VBA use a single singleton instance of Test?"
You'll have to treat it as a singleton, either by trying very hard to continually use only one instance, or make a "singleton wrapper" function holding a static variable which holds the object reference in your VBA code.
"if that Test class holds resources at the class level (i.e., static resources), when and how should you dispose of them?":
They are (typically) automatically cleaned up at application termination time, or when the application calls the COM object's 'finalize' method (if applicable to the internal workings of the COM class's library), or when otherwise the object's reference count drops to 0 (if applicable to the internal workings of the COM class's library).
I think a question which might provide better insight is: "How does the particular COM class I'm using handle its own lifespan?" Sometimes COM objects are perpetual, even living well beyond an end of a procedure within your calling application. Depending on the COM class design, the object could be made to remain alive regardless of if there's any remaining referencing objects within the calling application. Sometimes you would be required to explicitly destroy the object reference (using VBA 'delete' methods) or else the object could persist (as perhaps intended by designers of that particular COM class) as long as the calling application does. This is painfully evident by the persistence of ActiveX objects from website to website, and one of the main reasons why when browsing the net, it might be best to "trust no one". Even worse is if a perpetual object itself references other objects and resources internally.
To truly get a handle on the life-span of the class, you'd have to study documentation provided by the COM class authors. In this case, you'll need to devise a way from within your COM class library to enforce or provide externally accessible methods to enforce life-span standards (implementing IDisposable, offering up your own public Dispose() method, etc).
A reference link to the DLL certainly will remain active during the entire life-time of the calling application, being born at the moment you even tried to create the first object. AFAIK, there's nothing you can do about that. This is evident just by monitoring DLL application ownership info using some 3rd party system info application. This makes sense as it saves resources loading and unloading the DLL every time you need it.
