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.

 

[jcarlosd]

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.

Definitively, it works this way. Thanks a lot, ZVI!

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?

Sorry, rorya, but I did not understand what do you mean by loading the add-in. You are right that I use the dll to store functions and subs: what other possibilities are there? I would like to learn more about communication between Excel and DLLs.

JC

 

[RoryA]

Normally an add-in is loaded by the user and provides additional functionality to the main Excel interface. It is that loading that triggers the connection event for a COM add-in (or an automation one)
You appear to only call your dll from VBA rather than exposing functions for the user.