Question Use Excel VBA variable in VB.NET

mandeep242

New member
Joined
Jan 29, 2013
Messages
1
Programming Experience
1-3
Hello All,

I have a third party Excel Application, which have a VBA code, I want to fill the Excel Application from VB.NET. I want to update a VBA Boolean Variable during the Data Filling from VB.NET, is there is any way to do it?
 
I have a third party Excel Application, which have a VBA code, I want to fill the Excel Application from VB.NET. I want to update a VBA Boolean Variable during the Data Filling from VB.NET, is there is any way to do it?

Your VBA Macro code must be modified to read a parameter from a cell in say a new worksheet called "Parameters". Then your VB.NET code must find the Worksheet named "Parameters" and populate the cell. It is best that you apply a NAME to the cell and then use that NAME when writing the boolean value from your VB.NET software. Don't forget that the file name extension for the macro-enabled Excel file must be .xlsm. Then when from Excel you open the .xlsm file, the VBA Macro will start running and grab the boolean value you wrote into the "Parameters" worksheet. You can "protect" the "Parameters" worksheet and lock it from user editing if you don't want your users to mess with the parameters written by your VB.NET software.

The much more complex way to "pass" a value from a running VB.NET application to a VBA macro is to start Excel from your VB.NET code, load the .xlsm file into Excel, then use a complex COM message passing mechanism between your VB.NET application and the running Excel with active VBA macro. This will "break all the rules" for a VBA Macro in Excel since the VBA Macro must be written in such a way that it loops waiting for a COM message, and never exits. I do not know what that would do to the "performance" of Excel. I don't think it will like it at all! SO I recommend the much simpler approach I described first.
 
Last edited:

Latest posts

Back
Top