Excel c visual studio
Build Excel Automation Add-in: C#, VB.NET, C++
Automate Excel 2019 — 2002
Add-in Express™
for Microsoft® Office and .net
Excel Automation add-ins
Add-in Express allows creating Automation add-ins for Excel 2019, 2016, 2013 and lower. The example below demonstrates how you create such an add-in providing a sample user-defined function.
A bit of theory
Excel user-defined functions (UDFs) are used to build custom functions in Excel for the end user to use them in formulas. This definition underlines the main restriction of an UDF: it should return a result that can be used in a formula — not an object of any given type but a number, a string, or an error value (Booleans and dates are essentially numbers). When used in an array formula, the UDF should return a properly dimensioned array of values of the types above. Excel shows the value returned by the function in the cell where the user calls the function.
There are two Excel UDF types: Excel Automation add-in and XLL add-in. They differ in several ways described in What Excel UDF type to choose?
Per-user Excel UDFs
An Excel UDF is a per-user thing that requires registering in HKCU. In other words, a UDF cannot be registered for all users on the machine. Instead, it must be registered for every user separately.
Step 1. Creating an Automation add-in project
You start to develop your Excel Automation Addin with opening the New Project dialog in Visual Studio and navigating to the Extensibility folder.
Choose Add-in Express COM Add-in and click OK. This starts the COM Add-in project wizard. The wizard allows choosing your programming language (C#, VB.NET or C++) and specifying the oldest Office version your add-in needs to support.
Choosing a particular Office version will add corresponding interop assemblies to the project. Later on, in case you need to support an older or a newer Office version, you will be able to replace interop assemblies and reference them in your project. If you are in doubt, choose Microsoft Office 2002 as the minimum supported Office version (because Automation add-ins are supported in Excel 2002 — 2019). If you need background information, see Choosing interop assemblies.
Choose your programming language and the minimum Office version that you want to support and click Next. The wizard allows creating add-in projects targeting several Office applications. Since we create an automation add-in, naturally, we select Excel.
For the settings shown on the screenshot above, the project wizard will do the following:
- copy the corresponding version of Excel interop assembly to the Interops folder of your project folder
- add an assembly reference to the project
- add a COM add-in module to the project
- set up the SupportedApp property of the add-in module.
So, select Excel as the only Office application your automation add-in will support and click Next. On the next step, choose to generate new or specify an existing .snk file and click Next. The project wizard creates and opens a new Excel Automation Add-in solution in Visual Studio.
The solution contains only one project — the COM add-in project.
Step 2. Adding a COM Excel Add-in module
Open the Add New Item dialog for the COM add-in project and navigate to Excel below Add-in Express Items. In order to add Excel user-defined functions to the COM add-in, you choose the COM Excel Add-in Module in the Add New Item dialog.
Choose COM Excel Add-in Module and click OK. This adds the ExcelAddinModule1.vb file, if you have chosen VB.NET as your programming language or ExcelAddinModule1.cs if C# is your language of choice to the COM add-in project.
Step 3. Writing an Excel automation add-in function
In Solution Explorer, right-click ExcelAddinModule.vb (or ExcelAddinModule.cs) and choose View Code in the context menu.
Add a new public function to the class and write the code below:
Step 4. Running the Automation add-in in Excel
Choose Register Add-in Express Project in the Build menu, restart Excel, and check if your automation addin works.
If you use an Express edition of Visual Studio, the Register Add-in Express Project item is located in the context menu of the COM add-in module’s designer surface.
You can find your Excel add-in in the Add-ins dialog:
- in Excel 2000-2003, see Tools | Add-ins
- in Excel 2007, see Office Button | Excel Options | Add-ins | Manage «Excel add-ins» | Go
- in Excel 2010-2013, see File | Options | Add-ins | Manage «Excel add-ins» | Go.
Step 5. Debugging the Excel Automation add-in
To debug your automation add-in, specify Excel as the Start Program in the Project Options window and run the project.
Step 6. Deploying the add-in
The table below provides links to step-by-step instructions for deploying Excel Automation add-ins. Find background information in Deploying Office extensions.
How you install the Office extension
Per-user Excel UDF
Installs and registers for the user running the installer
Per-machine Excel UDF
Installs and registers for all users on the PC
ClickOnce
ClickTwice 🙂
ClickTwice 🙂
What’s next?
You can download this Excel Automation addin project as well as many other examples on the following pages: Excel VB.NET samples and Excel C# samples.
You may want to check the following sections under Add-in Express tips and notes:
- Development — typical misunderstandings, useful tips and a must-read section Releasing COM objects.
- Excel UDFs — many useful articles on developing Excel user-defined functions
How to interface with Excel in C++
Interfacing Excel in C++ is task that I needed to overcome recently, so I thought I would post some code and instructions on the said topic. Some online articles that I found to be useful include the following:
A Brief Introduction to C++ and Interfacing with Excel
Accessing Excel Spreadsheets via C++
Programming Excel COM Objects in C++
Without further ado, or huge swathes of boring theory, here are the steps needed to at least get you interfacing with Microsoft Excel via C++:
1: Ensure pre-requisite files are installed and located
We must ensure that a number of Microsoft libraries and their file locations have been installed and located, namely MSO.DLL, VBE6EXT.OLB and EXCEL.EXE . Make sure that you have a suitable version of Microsoft Excel installed, any version should do. In this example, Excel 2003 was used.
The locations of these files will obviously differ from machine to machine, so the first step is to determine their whereabouts. In Windows 7 for example, I recommend using Windows Explorer search facility. To locate (say) ‘ VBE6EXT.OLB ‘, click on the ‘Computer’ icon at the left, and in the edit box at the upper right simply enter ‘ VBE6EXT.OLB ‘ and let Explorer do the rest:
Right-click on the file and select ‘Open file location’. If you then click inside the file location edit box, this will give you the location you need, which you can then copy and paste:
In my example, the location of the ‘ VBE6EXT.OLB ‘ is
«C:Program Files (x86)Common Filesmicrosoft sharedVBAVBA6\VBE6EXT.OLB»
Do the same for the MSO.DLL and EXCEL.EXE files and you are then in a position to proceed to the next step.
2. Set up the Microsoft libraries
In order to use Excel functionality from within a C++ program we use the Microsoft Component Object Model (COM). The required import libraries are shown below and need to be included in any C++ application that interfaces with Excel.
Just use the file directories you found on your own computer for each #import , remembering to replace single backslashes (‘’) with double backslashes (‘\’), since the single backslash is classed as a special character:
rename is used to change certain strings in the import library to avoid clashes with other libraries containing functions, variables or classes with the same name. exclude prevents the import of specified items. no_dual_interfaces must also be included for the correct functioning of the Excel application function.
3. Use the Excel Object Model in your C++ code
The Excel Object Model contains a huge number of functions and objects. We will concentrate on just a few of those required to perform this tasks of reading from and writing to the Excel spreadsheet. Declaring an Excel Application Object pointer is simple enough:
Once created, we can use this pointer to open for reading and writing our Excel Workbook of choice:
Set the Visible parameter depending on whether you would like the Excel spreadsheet to be displayed or not:
This is how we can access the active Excel Worksheet and the cells within it:
To read the values of individual cells, simply use the Excel::RangePtr pointer above, remembering that in Excel cells must start from index = 1:
While writing and modifying individual cells is just as straightforward:
For example, I use the following simple Excel worksheet with a few modified cells:
I then run the example code in order to read the cell values, modify them and save the spreadsheet respectively:
Upon re-opening the spreadsheet “book.xls” notice how the cells have been updated and saved:
Full code listing: Office 2003 Example
Full code listing: Office 2013 Example
The following example was also seen to work on a more recent version of Excel (2013).
One or two differences in where the pre-requisite import files, executables etc were located but that’s about it:
One final thing – please be aware that any existing Excel processes still running in the background must be closed down before running this program, otherwise errors are likely to occur. For example you may be debugging this program and then decide to exit before pXL->Quit() is called:
