Excel VSTO and DNA project templates

There are ancient technology VBA for Excel, I used it since Excel was born, maybe this was 1995 year, currently I have a couple post about my project in this technology, for example project in this year My typical Excel VBA project (Excel VBA frontend can working with remote database) + admin panel., but currently I want to describe two modern type project for Excel with screenshot.

1. Excel VSTO.

I hope, this learning instruction can help for newbie to create VSTO application. Full code of this project template I have uploaded to Github https://github.com/AAlex-11/VstoVbExample.

So, first point for working with Excel is enable Developer menu and turn on/off of various COM-addins. Additional requirement is, of course, installing VSTO to Visual Studio.

If this 3 requirements is performed we can go forward and create project.

So, project created, our next step is adding Ribbon and place needed control on the ribbon.

We can then add a module that will contains the Excel add-in code. And place to Module two methods - Start and Stop.

Than usually I declared BckgroundWorker (I like it and use it always, therefore I have a lot of articles what is it and how it worked, for example Оновлення StatusLabel з потоку BackGroundWorker - приклад застосування Action, Delegate, Invoke, AddressOf, Extension, Expression.).

Pay attention that BackgroundWorker can declared with WithEvents, this allow Visual Studio create EventHadler by Click on combobox over code.

Than we can add 2 standard code line - Application.DoEvents and perform Start/Stop module methods.

In this point out application is ready to firstr start. We can add diagnostic message to console (to understand addins workflow) and can do first start our addins.

Cool, all environment going fine, Excel addins looking as workable. Next step is creating plugin meaningful code. For me, first step is remove restriction of visibility controls on Ribbons, therefore I usually changed Friend to Public.

Than we need add line Imports Microsoft.Office.Interop.Excel and need to reference to ActiveWorkBook, AcriveWorkSheet and finally Range.

That's is to start programming. In this example (for random colorizing cells) case code contains only 4 lines of code https://github.com/AAlex-11/VstoVbExample/blob/main/Module1.vb.

  13:              Dim RND As New Random()

  14:              For Each One As Range In Range1.Cells

  15:                  One.Interior.Color = RGB(RND.Next Mod 255, RND.Next Mod 255, RND.Next Mod 255)

  16:              Next

2. Excel DNA.

And this is the same example on ExcelDna - https://excel-dna.net/.

Please see my code on Github: https://github.com/AAlex-11/ExcelDnaExample.

Comments ( )