Question Use Excel VBA variable in VB.NET

[mandeep242]

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?

 

[aeskan]

Hello mandeep,

you can search and study about Excel Automation in Vb.Net in the following links.
How to automate Excel from Visual Basic .NET to fill or to obtain data in a range by using arrays
How to automate Microsoft Excel from Visual Basic .NET
Automating MS Excel Using Visual Studio .NET - CodeProject

 

[Rob Sherratt]

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.