Folks,
I have a Com/Activex internet client with exposed member functions that I can instantiate and access in Excel VBA. This client essentially does a kind of internet messaging, so part of the purpose is to receive events. So, per the example material included with this component, I am instantiating the object from the worksheet module rather than a standard module to receive events (messages, essentially).
Since I am currently taking the approach of putting as few control buttons on the sheet as possible, I have UserForms with buttons on them that are intended to access the member functions (methods) of the client to generate and send a message.
The core code in the Worksheet module is pretty familiar to most VBA programmers:
Public WithEvents oXObject As ComObject
****then****
Set oXObject = New ComObject
The problem is that even though the object variable is declared as Public in the Sheet's declarations section, if the messaging object is instantiated from the sheet module, it's functions are not available to a UserForm that is called. ComObject.function is not available to the code of a button on a UserForm. I get error messages on compile that tell me that no object exists, or at runtime, same thing.
(I've done a little searching on this and have come to the conclusion that, while it could be a pretty basic kind of thing, I'm not clear what to look for in a search.)
Is there a correct way to instantiate a Com/Activex object so that its events will be available on to a Sheet, but UserForms will be able to trigger its functions?
Thanks much,
Chris
I have a Com/Activex internet client with exposed member functions that I can instantiate and access in Excel VBA. This client essentially does a kind of internet messaging, so part of the purpose is to receive events. So, per the example material included with this component, I am instantiating the object from the worksheet module rather than a standard module to receive events (messages, essentially).
Since I am currently taking the approach of putting as few control buttons on the sheet as possible, I have UserForms with buttons on them that are intended to access the member functions (methods) of the client to generate and send a message.
The core code in the Worksheet module is pretty familiar to most VBA programmers:
Public WithEvents oXObject As ComObject
****then****
Set oXObject = New ComObject
The problem is that even though the object variable is declared as Public in the Sheet's declarations section, if the messaging object is instantiated from the sheet module, it's functions are not available to a UserForm that is called. ComObject.function is not available to the code of a button on a UserForm. I get error messages on compile that tell me that no object exists, or at runtime, same thing.
(I've done a little searching on this and have come to the conclusion that, while it could be a pretty basic kind of thing, I'm not clear what to look for in a search.)
Is there a correct way to instantiate a Com/Activex object so that its events will be available on to a Sheet, but UserForms will be able to trigger its functions?
Thanks much,
Chris
