VBA Conversion to excel.dna

[jonathan.n...@btopenworld.com]

Can somebody point in the right direction re 

The following - using Visual Studio 2012 Prof & Excel Dna installed via Nuget

Code as follows;

Imports XL = ExcelDna.Integration

Module Module1

  

    Private MenuObject As XL.CustomUI.CommandBarPopup

    Private MenuItem As Object

    Private SubMenuItem As XL.CustomUI.CommandBarButton

    Sub Setup_Utilities()

        MenuObject =

        MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=11, Temporary:=True)

        MenuObject.Caption = "&Test"

  

Etc.....

It doesn't work. Obviously I am doing some wrong. Any ideas?

J

[Govert van Drimmelen]

Hi Jonathan,

There are a few issues with the code you've shown:

* The Module needs to be Public for Excel-DNA to register the Subs and functions with Excel.

* With Excel-DNA, there is no 'ambient'  Application object - you need to get to the root Application object by calling ExcelDnaUtil.Application.

* Subs defined in .xll add-ins are hidden, so you would need to type the name of the Sub into the Alt+F8 box before you can run it.

I suggest you:

* Download the full Excel-DNA distribution from CodePlex (https://exceldna.codeplex.com) and then have a look at the various sample scripts under Distribution\Samples. They're a mix of C# and Visual Basic.

In particular, the CommandBars*** files in C:\Work\ExcelDna\Current\Distribution\Samples\Ribbon are VB.NET examples of different ways of loading CommandBars via Excel-DNA, which is what you are trying to do.

* You can also add references to the "Microsoft.Office.Interop.Excel" and "Office" assemblies to your project. That will allow you to get IntelliSense for the Excel COM Automation object model (start by assigning ExcelDnaUtil.Application to a variable Dimmed as Application from there).

* Also have a look at Patrick O'Beirne's guide to migrating from VBA to VB.NET (http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/) - he highlight some other issues, like dealing with constants like msoControlPopup.

I hope that gives you start. Please write back if you have more specific questions.

Regards,

Govert

[jonathan.n...@btopenworld.com]

Govert thanks for your very quick reply. I will have a go at this next weekend. PS I have some other issues which I may need to raise. PPS I have managed to convert most of my vba code to vb.net. I now in process of converting from vb.net to excel dna.

Best regards Jonathan

[jonathan.n...@btopenworld.com]

I have made spectacular progress since my last posting, having ditched VS2012 and used notepad and dna only. I can now programme in vba and convert directly to vb.net. I have successfully converted virtually all of my vba project to xl dna. I may now need to revert to VS2012 to sort out the PIA version issue and create forms to replace msgbox. Before I do this one minor niggle. 

How can I bring in graphics into my old style menu.

This is how I did it in VBA 

Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)

MenuItem.Caption = "Test"

MenuItem.FaceId = "1122"

MenuItem.OnAction = "Test1"

Its the FaceId I am interested in. I cannot figure out how to do this in the code I am now using in dna - as follows;

Dim myButtona As CommandBarButton = parent.Controls.AddButton()

        myButtona.Caption = "Test"

        myButtona.Style = MsoButtonStyle.msoButtonCaption

        myButtona.OnAction = "Test1"

PS Does any one have boiler plate code for forms which I can use in notepad and dna. I note that the sample files have boiler plate code for Panes.

PPS Is Visual Studio the only route to PIA version neutrality?

Using notepad with dna is ridiculously easy! Its a great product.

Thanks for your help

Regards Jonathan

[Govert van Drimmelen]

Hi Jonathan,

You should have no issues moving your code to Visual Studio.

The easiest way to start is to make a new VB.NET Class Library project, and then install the "Excel-DNA" NuGet package. That gives you a starter project into which you can just paste your code from the .dna files and everything should still work.

------------------

You should be able to say 

    myButtona.FaceId = 1122

(no quotes - it's an integer)

Does that not work?

--------------

>> PPS Is Visual Studio the only route to PIA version neutrality?

I'm not sure what you mean by this.

You can use the PIA assemblies from a .dna file too, but the main benefit is that you get the IntelliSense in Visual Studio. They are version-independent in exactly the same sense that VBA code is version-independent. If you write VBA code in Excel 2010, only the features available in Excel 2010 will be supported, but your VBA code can run on Excel 2013 too, and even on Excel 2007 as long as you did not use objects or methods that are not available on Excel 2007. Likewise, if you reference the Excel 2010 PIA assemblies in your VB.NET project, it will work the same way under other Excel versions.

Please write back if this is unclear or I need to expand more on this.

Regards,

Govert

[jonathan.n...@btopenworld.com]

[jonathan.n...@btopenworld.com]

Govert thanks for your help

I have just tried myButtona.FaceId = 1122 in dna & I am sorry to report that it doesn't work.

Maybe I have missed some reference?

Best regards

Jonathan

On Sunday, 9 February 2014 17:51:01 UTC, jonathan.n...@btopenworld.com wrote:

[Govert van Drimmelen]

Did you change the Style as well? 

It might need to be msoButtonIconAndCaption or something.

-Govert

[jonathan.n...@btopenworld.com]

Govert

 I just remarked out the style and it all worked  eg  '        myButton10.Style = MsoButtonStyle.msoButtonCaption

Also I have set up my project in VSTO 2012 using Nuget etc, have copied across my code from notepad into VSTO and am again absolutely amazed at how easy it all went.

Further more having read Patrick O'Beirne's excellent write up was expecting to have to do more difficult work to get to a single_ packed_file.xll but am amazed to find it just sitting there in the VSTO Debug folder along with the 64 bit version. All I have to do now is figure out how to pack in the PIA? Once I have done this I will test the packed_file.xll on a spare XP Excel 2003 to see if it works. Thanks to you for your excellent product.

Regards Jonathan

[Govert van Drimmelen]

Hi Jonathan,

If your references to the PIA assemblies are marked as "Embed Interop Types=True" then you don't have to do anything to pack them - the compiler already puts the bits needed inside your .dll.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of jonathan.n...@btopenworld.com [jonathan.n...@btopenworld.com]
Sent: 10 May 2014 02:31 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: VBA Conversion to excel.dna

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.