In this post we are going to take a look at how we can work with the Excel Object Model (OM) via a DNA addin.
First create a new DNA solution in SharpDevlope, and import the Excel DNA Integration DLL, as we did before, etc, etc, etc. (See last post)
To get to the object model we only need 1 line of code!
Add this inside a new class, build it and add the DNA and XLL files as needed (see the last post for a how to, if you need to) This function will return the username, from the Excel object model, see easy!
Public Shared Function UserName (ByVal sString As String) As String Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application Return XLApp.Username End Function
Lets try some other things! Try this code:
Public Shared Sub WNC
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
Dim x As String
x = inputbox ("what do you want to call the active worksheet?", "EXCEL DNA TEST")
XLAPP.activesheet.name = x
End Sub
This changes the sheet name of the active workbook, the input box is from dot net not Excel. Here’s a range example:
Public Shared Sub Range
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
XLApp.range("a1:a10").select
msgbox(XLApp.selection.cells.count)
End Sub
Cool, so there you go that’s how to get at the object model.
One other thing you might like to think about is how to put this to use in a project. What I do is add a standard module, and inside that declare a variable like this:
Public Module Globals Friend XL as Object End Module
Then use the auto open event to set a refeance to this variable, from which it can be used through the project. Like this :
Imports ExcelDna.Integration
Class StartUp
Implements IExcelAddIn
Public Sub Start() Implements IExcelAddIn.AutoOpen
XL = ExcelDna.Integration.ExcelDnaUtil.Application
End sub
Public Sub Close() Implements IExcelAddIn.AutoClose
'Fires when addin is removed from the addins list
'but not when excel closes - this is to
'avoid issues caused by the Excel option to cancel
' out of the close 'after the event has fired.
msgbox ("Bye bye, from DNA")
End sub
End Class
Anyway there’s something for you to be thinking about for the next week! Enjoy
Ross
I haven’t tried it yet, but noticed that you’re using XLAPP and XL in the third example (RANGE). Is that an error, or am I missing something?
Thanks Rob, yes, the variables should be the same, updated now.
Ross,
Why no intellisense when I type xlApp.
I just get a very small list which does not have range as an object
Ross, Thanks a lot for this post.. It really makes me believe that a VBA programmer can now jump in to .net without spending money on V$tuido like products
Hi Sam,
It’s late binding so there’s no OM intellisense, but you should be able to early bind then switch before you do the last compile…. But I’m not too sure about all this, so will look at it later – on holiday this week though…
oh, and thanks for the feedback
Ross
Ross,
Can you also do a post on how to create a custom Tab in Excel 2007 using sharp + Excel DNA – so that the XML does not have to stay in a Text file
Ross,
When I have more time I will investigate it further. It’s looks very nice and I can understand why some people, like sam, see a great potentiality in it.
Thanks,
Dennis
Ps: Would it be possible that You make the demo available?
Hi Dennis, yeah I’ll upload something next week – I’m working on the install thing we talked about a few weeks ago.
Thanks
Ross
Thanks a lot!!! I managed to do a couple of things already with this last post (been trying my UDFs). Some questions though:
1. I don’t get intellisense either. Do you know how to fix it?
2. How are you exposing the procedures to Excel? Commandbars, custom ribbons, autoopen….? It’d be nice to know if there’s a more effective way to do it.
3. Is there any way to use office’s enum types? I mean, setting a variable to a “msoSomething” value.
ps: Govert, if you’re reading this, these posts would make for a great introduction for ExcelDNA, you should include at least a link in the next release.
Hi,
1. see Sam’s question,
2. all of the above, will cover these at some point, you can call “subs” by entering the name in the macro dialog though.
3. not that I know off, but not too sure. Might be worth asking the question over on the goggle group?
Thanks
Ross
Hope this hasn’t already been answered (I’ve read through all the posts and can’t remember).
I used this method to get at the drop down menus. Unfortunately you still have to use object to get at the extensions. So I guess I’ll still have to have one set of variables to get to the code and another set for the final code (I’ll just use a global variable that gets the reference and then change the variable back to dna’s variable). Kind of a pain. It would be interesting to get the two to place nice with each other.
http://devcity.net/Articles/163/1/Articles.aspx?a=ViewArticle&ArticleID=163&Page=1
[...] Excel DNA and the Excel Object Model « Layer model diagram – a new way to view and test your Application LikeBe the first to like this post. [...]
Nice and useful article for beginners, thank you. Would you please show the C# versions, too? Just for dummies (me).
Hi Ross, are you still working on Excel DNA?
Its not my product it Goverts, and ye he is still working very hard on it and it’s going from strength to strength!
Thanks, Ross!
I wish I had paid more attention when you showed this at the XL Dev Conf a year ago – it’s simpler than it looks!
just one small question: how can I copy code from your blog with line breaks? It comes across all on one line.
Thanks
Patrick
Hi Patrick,
Try pasteing into note pad before VS, should work ok, watch out for “”, might have to find & replace them…
Thanks
Ross