Changing Excel options from outside DLL

[jcarlosd]

Hello

I have been using a simple VBA code to change Excel international options to what my application runs. Basically it sets DecimalSeparator = "." and ThousandsSeparator = ",".

I decided to move my VBA code to a DLL written in VB-6.
What I do is to connect to Excel with a reference library and later I write this in my class:
Implements AddInDesignerObjects.IDTExtensibility2
Private oXL As excel.Application

I try later in my function to change the Excel options with this code:
Set oXL = excel.Application
oXL.DecimalSeparator = "."
oXL.ThousandsSeparator = ","

However these options are changed outside my "current" Excel application. It will not work until next time I re-open Excel. This is not what I desire: I want to be able to change immediately Excel options.

How can I do this? Anybody has experience with this problem? I checked in the forum and using Google too, but could not find any solution.

 

[RoryA]

The OnConnection event of IDTExtensibility2 provides you with a reference to the Application object and you should assign that to your oXL variable.

 

[jcarlosd]

I do have a OnConnection section in my DLL. It contains this code:
Private Sub IDTExtensibility2_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)
Set oXL = Application
End Sub

Do you mean that I should include the change of the Excel options inside this event?

JC
BTW: Thanks for such fast reply!

 

[RoryA]

Within that event you should have:

Set oXL = Application

and you can then manipulate the object as and when required.

I should add that if this is an add-in for distribution, this is extremely bad practice.

 

[jcarlosd]

I should add that if this is an add-in for distribution, this is extremely bad practice.

I was really shocked with your statement, because I would like to develop "good code". My add-in is connecting to external applications that work always with decimal-separator="."
However, in my country, Excel is automatically installed with decimal-separator=","

Of course, when a user runs my add-in, I check what configuration he/she has, change to decimal-separator=".", and at exiting, it restores the initial configuration.

Do you think it is a bad practice? It was my idea of letting the user to have Excel in their own way and make it work. But I will admit any better solution.

JC

 

[RoryA]

IMO, anything that alters user settings is bad practice. In an ideal world, your addin should read the user's settings and react accordingly, not alter them to what you would like them to be.

 

[jcarlosd]

I totally agree. So, maybe I have to rethink about my development.
For me it was pretty simple to setup always the decimal-separator="."

So, later, If I connect to a European web page with numerical data I know that numbers will be with decimal commas, and if they are American they will use decimal points. But the retrieval of data will be treated always the same way.

Like I said: I have to think on it again.
I really thank you for your help and ideas

JC

 

[jcarlosd]

Within that event you should have:

Set oXL = Application

and you can then manipulate the object as and when required.

I tried your suggested solution but it seems that the onConnection event is never triggered. I wrote in the class module of my DLL (under the :

Private Sub IDTExtensibility2_OnConnection( _
        ByVal Application As Object, _
        ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
        ByVal AddInInst As Object, custom() As Variant)
Set oXL = excel.Application
MsgBox "Addin initiated"

Code in the Excel VBE is:

Sub test()
Dim myObj As myAddin.myClass
Set myObj = New myAddin.myClass
myObj.myOptions
End Sub

But, like I said, it is not showing the MsgBox

Any idea about how to trigger the OnConnection event?

JC

 

[RoryA]

The OnConnection event is triggered when you load the add-in from the Excel UI. It appears you are not actually doing this, but merely using the dll as a function library for your code? If so, I suggest you should implement a method or property in your class that allows you to pass in an application object for it to manipulate.

 

[ZVI]

Try to pass Application from VBE as parameter to the DLL init subroutine (build it public) in which do setting of oXL object to that parameter.