none
Interlinking VSTO & VBA; migrating VBA to VSTO for use with VBA RRS feed

  • Question

  • Since i cant cast Interop to Tools and vice versa.  Is there a best way to utilize the two so i can get the functionality needed?

    I want to port several classes developed in VBA to .Net (easier development for me) and then consume the class(es) inside VBA.  So far i have been unable to perform an Initialization of the test classes inside VBA and the initialization inside .Net has been spotty at best during the Open Workbook series of events.

    So what is the best approach of developing a class, or more, using VSTO to be consumed in VBA (Office 2007/2010)?


    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break softare to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    • Split by Cindy Meister MVPModerator Friday, July 13, 2012 2:58 PM new question, split from http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/d3efe806-67d0-4494-a26f-78189aec876c#aa7686e2-31cf-4569-b052-c1dc79165550
    Friday, July 13, 2012 2:22 PM

Answers

  • Hi goldbishop

    One thought does occur to me on seeing this code...

    I wouldn't place the "initialization" in the workbook VBA. Two reasons:

    1. You can't be 100% sure that the VSTO DLL will actually execute (if the workbook were mailed to an outside person without the DLL installed, for example). Not a good idea, beside any other issues, that a VBA macro is also erroring out.

    2. You can't guarantee that this macro will trigger after the VSTO solution is completely loaded. Better IMO would be to have code in VSTO's WorkbookOpen event (not ThisAddin_Startup) that uses Application.Run to call the VBA macro to initialize communication between VBA and the workbook solution.


    Cindy Meister, VSTO/Word MVP

    Thursday, July 19, 2012 11:35 AM
    Moderator

All replies

  • Hi goldbishop

    I've split this off into a separate discussion since it's a definite change of topic and I think you'll profit best from input from many people. No one would see this question at the end of that other thread...

    I'm still having difficulty following the leaps of logic your brain is making :-) But from what I can glean, it seems to me that what you may need is a separate managed code COM DLL that VBA can "consume". Can you list some of the things the VBA code that you want to port into "VSTO" does?


    Cindy Meister, VSTO/Word MVP

    Friday, July 13, 2012 3:02 PM
    Moderator
  • Sure....and you have it just about right, i am hard to follow thinking wise when i type, wish i could PowerPoint this with "pretty pictures" ;)

    Doc.xlsm (VBA):
    - clsSettings.vb

    '****** Do Not Insert above this Comment *******
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' (5/22/2012) CODE COMPLETE, REDOCUMENT (in NotePad) IF ANY CHANGES ARE MADE
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Option Explicit
    Option Base 1
    
    ''''''''''''''''''''''''''''''''
    ' Class Variables
    ''''''''''''''''''''''''''''''''
    Private p_Brand As clsBrand
    Private intLock As Long
    Private intMode As Long
    Private strMode As String
    
    ''''''''''''''''''''''''''''''''
    ' Class (De/Con)structors
    ''''''''''''''''''''''''''''''''
    Private Sub Class_Initialize()
       Initialize
    End Sub
    
    ''''''''''''''''''''''''''''''''
    ' Property Information
    ''''''''''''''''''''''''''''''''
    ' BrandName
    Public Property Get BrandName() As String
    '****** Do Not Insert above this Comment *******
       BrandName = p_Brand.name
    End Property
    
    ' BrandID
    Public Property Get BrandID() As Long
    '****** Do Not Insert above this Comment *******
       BrandID = p_Brand.id
    End Property
    
    'Brand Abbreviation
    Public Property Get BrandAbbrev() As String
    '****** Do Not Insert above this Comment *******
       BrandAbbrev = p_Brand.Abbrev
    End Property
    ' Lock
    Public Property Get LockID() As Long
    '****** Do Not Insert above this Comment *******
       LockID = intLock
    End Property
    
    'Mode
    Public Property Get ModeID() As Long
    '****** Do Not Insert above this Comment *******
       ModeID = intMode
    End Property
    
    Public Property Get ModeName() As String
       ModeName = strMode
    End Property
    
    Public Property Let ModeName(val As String)
       strMode = val
    End Property
    
    ''''''''''''''''''''''''''''''''
    ' Subroutine Information
    ''''''''''''''''''''''''''''''''
    Public Sub Initialize()
    '****** Do Not Insert above this Comment *******
       Dim wb As Workbook: Set wb = ActiveWorkbook
       
       'Unlock Worksheet
       Dim wssel As XlEnableSelection: wssel = EnableSelect(Worksheets("WBData"))
    
       Set p_Brand = New clsBrand
       
       'Obtain file mode
       intMode = CLng(GetSetting("wb_mode"))
       
       'Obtain file Lock
       intLock = CLng(GetSetting("wb_lock"))
       
       'Obtain file Brand
       p_Brand.Initialize CLng(GetSetting("wb_brand"))
    
       'Obtain Brand Mode Name
       strMode = GetBrandMode(intMode)
    
       'lock worksheet
       DisableSelect Worksheets("WBData"), wssel
    
    End Sub
    
    
    Sub UpdateLockID(val As Long)
    '****** Do Not Insert above this Comment *******
       Dim cell As Range
       Set cell = Worksheets("WBData").Range("cellWBLock")
       
       intLock = val
       
       cell.Value = val
       
    End Sub
    
    ''''''''''''''''''''''''''''''''
    ' Method Information
    ''''''''''''''''''''''''''''''''
    Private Function GetSetting(key As String) As String
    '****** Do Not Insert above this Comment *******
       Dim rng As Range
       Set rng = Worksheets("WBData").Range("rngWBSettings")
          
       GetSetting = CStr(Application.WorksheetFunction.VLookup(key, rng, 2, False))
    End Function
    
    

    I would like to port this Class definition to a Workbook Project not attached to a HostItem (which seems to be the only way to expose this functionality). There are at least 2 other classes in my VBA Workbook Project that i dont want to attach to a HostItem but need to be consumable within VBA of the next version.

    VSTO Workbook Project Class Defitions:

    - WBSettings.vb

    Option Explicit On
    
    <Microsoft.VisualBasic.ComClass()> _
    <System.Runtime.InteropServices.ComVisible(True)> _
    Public Class WBSettings
    	Implements IWBSettings
    
    #Region "Variables"
    	Private _wb As ThisWorkbook
    	Private _brandname As String
    	Private _brandid As Integer
    	Private _brandabbrev As String
    	Private _lock As Integer
    	Private _mode As Integer
    	Private _mName As String
    #End Region
    
    #Region "Properties"
    	Public ReadOnly Property BrandName As String Implements IWBSettings.BrandName
    		Get
    			Return Me._brandname
    		End Get
    	End Property
    	Public ReadOnly Property BrandID As Integer Implements IWBSettings.BrandID
    		Get
    			Return Me._brandid
    		End Get
    	End Property
    	Public ReadOnly Property BrandAbbrev As String Implements IWBSettings.BrandAbbrev
    		Get
    			Return Me._brandabbrev
    		End Get
    	End Property
    	Public Property LockID As Integer Implements IWBSettings.LockID
    		Get
    			Return Me._lock
    		End Get
    		Private Set(value As Integer)
    			Me._lock = value
    		End Set
    	End Property
    	Public Property ModeID As Integer Implements IWBSettings.ModeID
    		Get
    			Return Me._mode
    		End Get
    		Private Set(value As Integer)
    			Me._mode = value
    		End Set
    	End Property
    	Public Property ModeName As String Implements IWBSettings.ModeName
    		Get
    			Return Me._mName
    		End Get
    		Set(value As String)
    			Me._mName = value
    		End Set
    	End Property
    #End Region
    
    #Region "Constructor/Destructor"
    	Public Sub New()
    
    	End Sub
    #End Region
    
    #Region "Subroutines"
    	Public Sub Initialize() Implements IWBSettings.Initialize
    		Dim wb As ThisWorkbook = Globals.ThisWorkbook
    
    		''Dim wssel As Excel.XlEnableSelection = wb.Worksheets("WBData").EnableSelection
    		''wb.Worksheets("WBData").EnableSelection = Excel.XlEnableSelection.xlNoRestrictions
    
    		Me._mode = CInt(GetSetting("rngWBSettings", "wb_mode"))
    		Me._mName = GetSetting("rngBrandModes", CStr(Me._mode))
    		Me._lock = CInt(GetSetting("rngWBSettings", "wb_lock"))
    		Me._brandid = CInt(GetSetting("rngWBSettings", "wb_brand"))
    		Me._brandname = GetSetting("rngWBBrand", CStr(Me._brandid), 3)
    		Me._brandabbrev = GetSetting("rngWBBrand", CStr(Me._brandid))
    
    	End Sub
    
    	Public Sub UpdateLockID(val As Integer)
    		Dim cell As Excel.Range = Me._wb.Names.Item("rngWBSettings")
    		If cell IsNot Nothing Then
    			cell.Value = val
    
    			Me.LockID = val
    		End If
    	End Sub
    #End Region
    
    #Region "Functions"
    	Private Function GetSetting(rng As String, key As String, Optional idx As Integer = 2) As String
    		Dim erng As Excel.Range = Me._wb.Names.Item(rng).RefersToRange
    
    		Return CStr(Me._wb.Application.WorksheetFunction.VLookup(key, erng, idx, False))
    	End Function
    #End Region
    End Class

    - IWBSettings.vb

    <Runtime.InteropServices.ComVisible(True)> _
    <Runtime.InteropServices.InterfaceType(Runtime.InteropServices.ComInterfaceType.InterfaceIsDual)> _
    Public Interface IWBSettings
    
    	'Properties
    	ReadOnly Property BrandName As String
    	ReadOnly Property BrandID As Integer
    	ReadOnly Property BrandAbbrev As String
    	Property LockID As Integer
    	Property ModeID As Integer
    	Property ModeName As String
    
    	'Subroutines
    	Sub Initialize()
    End Interface

    This is the simplest design structure i have.  I also have Modules in the source Workbook VBA project that i want to remove from VBA and put in a .Net library, most can be consumed by any Excel Workbook/Worksheet but i need to start small and understand this before i start moving further out.

    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break softare to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken


    • Edited by goldbishop Friday, July 13, 2012 4:06 PM added 'clsSettings.vb' above the top-most code segment
    Friday, July 13, 2012 4:05 PM
  • Hi goldbshop,

    Thanks for posting in the MSDN Forum.

    Seems you have foud some work round in your last reply. Is it right?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 16, 2012 6:28 AM
    Moderator
  • Hi Tom

    No, he doesn't have an answer, yet. The most recent post is providing more information about the type of thing he wants to achieve. It's basically sample code.


    Cindy Meister, VSTO/Word MVP

    Monday, July 16, 2012 9:31 AM
    Moderator
  • Hi goldbishop

    OK... It's a bit much to digest, especially since my area of expertise is Word (so I understand code more instinctively in that object model), but let's see...

    If I'm seeing this correctly, the key point (at the moment) is here?

    Public Sub Initialize()
    '****** Do Not Insert above this Comment *******
      
    Dim wb As Workbook: Set wb = ActiveWorkbook
      
      
    'Unlock Worksheet
      
    Dim wssel As XlEnableSelection: wssel = EnableSelect(Worksheets("WBData"))

      
    Set p_Brand = New clsBrand

    My first question is: Where's the definition of clsBrand? Are you declaring a class in VBA without having a class module for it? (Just trying to understand how the original is designed)

    And I'm guessing that this part is what's being a pain on the VSTO side - dealing with the workbook itself? Is Initialize being called from the VBA code? And that's being called from ThisWorkbook_Open? Then...

       Dim wb As Excel.Workbook = CType(Globals.ThisWorkbook, Excel.Workbook)

    Anytime you have to pass the workbook object between VBA and the VSTO code it needs to be passed (and received) as type Object.

    Does this get you any further?


    Cindy Meister, VSTO/Word MVP

    Monday, July 16, 2012 9:44 AM
    Moderator
  • - clsBrand is a similar class, in the current document, but i am going to centralize the information into the WBSettings class.  It holds 3 pieces of information, basically, in it (ID, Name, Abbreviation) with some accessor methods to that data.  I am going to remove that class definition and consolidate it into the Settings class (WBSettings); as its original purposes has since been removed from the scope of the workbook.

    - Initialize is one method, that i am working on transitioning, that is causing alot of frustration.  The WBSettings class will need to read information from the WBData worksheet but also be consumed on other sheets on its own.  WBData is the settings resource location for most of the workbooks security settings.  There are 3 subsequent worksheets (hidden to the end-user) that carry data specific for the Brand setting on WBData. (Design once, Use many).

    Also, i see the use of 'Excel.<object>', is 'Excel' from the 'Tools.Excel' or 'Interop.Excel'?  In my dev environment, i am trying to explicitly associate one or the other via a Namespace prefix 'TExcel' = 'Tools.Excel' and 'IExcel' = 'Interop.Excel'.  To help me further understand which object set is being used for which operations.

    Further understanding on my part:
    - When a TExcel object states that its property is returning a IExcel object, how should i handle that on VSTO side?  As well, if i want to expose a VSTO developed Class, Method, Property, etc. to the VBA side of the Workbook, do i need to make the return value be of Object or can i specify the actual Object? (aside from Data-Type objects)


    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break software to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    Monday, July 16, 2012 1:27 PM
  • Hi goldbishop

    <<There are 3 subsequent worksheets (hidden to the end-user) that carry data specific for the Brand setting on WBData>>

    As long as you're revamping everything, it would probably be a good idea to store this information in a Custom XML Part rather than hidden worksheets. For one thing, three fewer worksheets in the workbook :-) And that makes a lot less code in the VSTO project, as well, since VSTO will generate classes and code for each worksheet. For that reason, things would execute more quickly, as well as the fact that working with CustomXMLParts will tend to be faster than manipulating "the interop" in order to retrieve each piece of information.

    <<Also, i see the use of 'Excel.<object>', is 'Excel' from the 'Tools.Excel' or 'Interop.Excel'?  In my dev environment, i am trying to explicitly associate one or the other via a Namespace prefix 'TExcel' = 'Tools.Excel' and 'IExcel' = 'Interop.Excel'.  To help me further understand which object set is being used for which operations.>>

    In my little snippet, it's Interop.Excel. Good idea, using the two prefixes :-) VBA won't understand anything about TExcel, so anything you want VBA to work with needs to be IExcel.

    <<When a TExcel object states that its property is returning a IExcel object, how should i handle that on VSTO side?>>

    Could you give me an example, please? Just to be sure we're on the same wavelength... But FWIW I almost always work with the Interop objects unless I specifically want to use something that VSTO offers to me, such as databinding to a Range on the sheet or a ListObject.

    <<As well, if i want to expose a VSTO developed Class, Method, Property, etc. to the VBA side of the Workbook, do i need to make the return value be of Object or can i specify the actual Object? (aside from Data-Type objects)>>

    COM VBA <-> .NET Framework (that's where the real problem lies) communicate correctly with basic data types such as strings and numerical values (although Integer and Long represent a different range of values, as I imagine you're aware). Anything and everything else must be passed as"Object", even if it's an IExcel-"thingy". Ditto for arrays. The problem is the interface you're passing things through; .NET and COM are just different.


    Cindy Meister, VSTO/Word MVP

    Monday, July 16, 2012 3:40 PM
    Moderator
  • On the "Revamping", VSTO <-> VBA is new to me and my current job requires me to engulf myself in the interaction. 

    I know COM & .Net are two competing brothers that dont really want to talk to each other without some kind of mediator but just the same i know i can find a common path of communication which is ultimately where i am going.  Once i find that way of communicating between the VSTO .Net and the VBA COM frameworks then i will be in business.

    As far as, TExcel objects returning IExcel references, look at TExcel.Workbookbase as a point of reference where properties like `ActiveChart`, `Names`, and `Worksheets` return a object that is from the IExcel namespace.  This is where my confusion really comes into play where the two namespaces (TExcel & IExcel) are seemingly implemented synonomously.

    So that i understand:
    - All VSTO classes need to incorporate the IExcel framework throughout with no reference to TExcel.
    - All VSTO Classes that are going to a return value to VBA need to return the simple data-type or Object (for complex types).

    VBA Implementation questions:
    1) What attributes do i need to attach to the class(es) to make them visible to the VBA side of the Workbook?
    2) Do i need to attach any attributes to the value returning properties/methods of the VSTO classes in order to use them in VBA?
    3) What infrastructure do i need to implement inside VBA to instantiate and consume classes developed in VSTO, outside of adding reference to the library?


    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break software to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    Monday, July 16, 2012 4:18 PM
  • Hi goldbishop

    I'm going to have to take this in two parts, as I need to do some research to answer the 3 VBA implementation questions. But as to the other parts...

    The link you give me refers to this MSDN article:
    http://msdn.microsoft.com/en-us/library/bb386206.aspx

    And under "Understanding the design of generated classes" it explains, briefly, what the base class is and how things "mesh". If you want a really detailed discussion, I recommend "VIsual Studio Tools for Office 2007" by Carter & Lippert. It doesn't cover the .NET 4.0 aspect, but it does go into great depth about how VSTO works "under the covers".

    <<- All VSTO classes need to incorporate the IExcel framework throughout with no reference to TExcel>>

    If I'm understanding correctly, the answer is "No", this is not correct. Your classes can use TExcel, but you can't pass any TExcel objects to VBA and expect them to work. No problem with using them solely within the classes.

    <<- All VSTO Classes that are going to a return value to VBA need to return the simple data-type or Object (for complex types).>>

    Correct.


    Cindy Meister, VSTO/Word MVP

    Tuesday, July 17, 2012 4:14 PM
    Moderator
  • Thanx ill see what i can find on the book.  I like knowing the inner workings, helps me understand how to lay out the designs if i know ahead of time the intracies and quirks of the system.

    Ill be more than happy to do some researching as well, i prefer hand-ups not hand-outs.


    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break software to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    Tuesday, July 17, 2012 4:37 PM
  • As to the last three questions, in what way does the information in this article not cover your first question?

    http://msdn.microsoft.com/en-us/library/bb608604.aspx

    (2) I'm not sure I understand the question.

    (3) VSTO should take care of adding the reference for you. What VSTO does is explained in the above artice and
    http://msdn.microsoft.com/en-us/library/bb386306.aspx


    Cindy Meister, VSTO/Word MVP

    Tuesday, July 17, 2012 4:37 PM
    Moderator
  • The "Expose Code to VBA..." article, explains the UI properties to set, and i can get exposure of the class(es) but either i am consuming them incorrectly or not right.

    (2) Think the object reference solved this question

    (3) Is more of a end-user implementation question, the DLL created from the Workbook project, is obviously not packages with the Workbook and is seperate to its own.  Do i need to wrap the DLL into a MSI installer or is the VSTO (extension file) what i need to deploy to the end-users?  I have seen several that skirt packaging the whole Release directory (per say), and others that just publish.  I want to be able to deploy the solution reliably.  What concerns or steps do i need to take when deploying the VSTO solution to make it "seemless", without using ClickOnce?


    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break software to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    Tuesday, July 17, 2012 4:50 PM
  • Hi goldbishop

    On point 1, could you please provide a small repro example so that I can follow exactly what it is you're trying to do? And please indicate exactly how it's not doing what you need.

    (3) See the article posted at the top of the forum: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/1666d2b0-a4d0-41e8-ad86-5eab3542de1e


    Cindy Meister, VSTO/Word MVP

    Wednesday, July 18, 2012 5:14 PM
    Moderator
  • TestWB.xlsm ThisWorkbook.vb:

    Private Sub Workbook_Open()
       MsgBox "Workbook Open", vbOKOnly
    '   CallVSTOAssembly.InitSettings
    End Sub
    
    Public Sub Test()
    
    End Sub
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' DO NOT REMOVE BELOW - VSTO Object properties for 'ThisWorkbook'
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''' LockID Update '''''''''''
    Public Sub UpdateLock(val As Long)
       CallVSTOAssembly.UpdateLockID val
    End Sub
    '''''''' Assembly Access '''''''''
    Property Get CallVSTOAssembly() As TestWB.ThisWorkbook
        Set CallVSTOAssembly = GetManagedClass(Me)
    End Property
    Ill have to post the VS2010 project view of the ThisWorkbook later, cause i had made some changes  from the other post and nothing works at all ;)  Plus my viewpoint on integration has changed since our original conversation.

    "I am the reason, Curiosity killed the Cat!"
    Please be patient, there are times where i do not respond for weeks at a time.
    Developer Fusion Tool
    Definitions:
    Hackers - Break software to better its use and functionality.
    Crackers - Break software to be malicious and leave it broken

    Wednesday, July 18, 2012 5:50 PM
  • Hi goldbishop

    One thought does occur to me on seeing this code...

    I wouldn't place the "initialization" in the workbook VBA. Two reasons:

    1. You can't be 100% sure that the VSTO DLL will actually execute (if the workbook were mailed to an outside person without the DLL installed, for example). Not a good idea, beside any other issues, that a VBA macro is also erroring out.

    2. You can't guarantee that this macro will trigger after the VSTO solution is completely loaded. Better IMO would be to have code in VSTO's WorkbookOpen event (not ThisAddin_Startup) that uses Application.Run to call the VBA macro to initialize communication between VBA and the workbook solution.


    Cindy Meister, VSTO/Word MVP

    Thursday, July 19, 2012 11:35 AM
    Moderator