Solved

Pass Variable between VBA and VB.net

Posted on 2014-03-22
12
1,233 Views
Last Modified: 2014-03-24
Hi All,

I am trying to pass variables between VBA and VB.Net - I have gotten as far as being able to call my VB.Net function from VBA and display a message box stating whether or not the VB.Net Function is True/False.

But for the life of me I can't find how to pass the variable between VBA & VB.Net

' IN VSTO
Imports System.Data
Imports System.Runtime.InteropServices
Imports Word = Microsoft.Office.Interop.Word
<ComVisible(True)> _
Public Interface IAddInUtilities
    Sub ImportData()
End Interface

<ComVisible(True)> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class AddInUtilities
    Implements IAddInUtilities

    Public valid As Boolean
    Public Sub ImportData() Implements IAddInUtilities.ImportData
        If LicenseValid() Then
            MsgBox("VSTO - Valid")

        Else
            MsgBox("VSTO - Invalid")

        End If

    End Sub
End Class

Open in new window



And...
'IN VBA
Sub CheckDataValid()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("WordAddIn1")
    Set automationObject = addIn.Object
    automationObject.ImportData
    
' Right here I want to be able to use the result of the check
If CheckDataValid then
  msgbox "T"
Else
  msgbox "F"
end if

End Sub

Open in new window


Thanks in advance
0
Comment
Question by:DrTribos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
12 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39947428
1. Are you trying to pass data from VBA to VB.Net or from VB.Net to VBA?  It looks like the latter.
2. The LicenseValid() isn't defined in your example code.
3. You are invoking CheckDataValid from within the CheckDataValid routine.  Although this is permissible under the rules of program recursion, I don't think it is what you want to do.
4. Is it safe to assume that your msgbox statements are purely for diagnostic purposes?

Are you trying to perform some license check on one side and pass that result to the other side?
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39947953
Hi Aikimark,

Thanks for stopping by, answers in order:

01.

Ultimately I want to know how to do both. In this case I want to pass a boolean from VB.Net to VBA

02.

The LicenseValid is a procedure on a different tab in VS.  At the moment LicenceValid is called by Public Sub ImportData() in VSTO and the result is accessable to Public Sub ImportData() - thus I did not think I needed to include it, appologies if that was unclear (let me know if it is still unclear)

03.

Ahh, you are referring to lines 9-14, sorry that was for illustration and should be comments.  I have not actually run with that code...  erm would that work?  

04.

Yes, the message boxes are there so I can see what is happening.  I have been using. This is the 3rd time I have opened VS... (need I say more?)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39948555
would that work?
No.

Perhaps the easiest way to pass the data is for the .Net code to alter some property of a cell (True|False).  Then the VBA code could inspect that property.

I recently recommended using a cell's ID property.
http:Q_28386759.html#a39925691
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 15

Author Comment

by:DrTribos
ID: 39949056
Thank you for the suggestion.  I am working mainly with MS-Word.  

I don't understand why I would want to use a cell's ID property; are you telling me that it is possible to call a VB.Net ComAddIn from VBA but it is not possible to pass variables?

The back story to this question is that I have a large VBA project that I want to move to VB.Net.  I can't do it over night but if I can pass variables between VBA and a VB.Net ComAddIn then I can work on it gradually.

Thanks,
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39949129
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39949179
I hope the following illustrates conceptually what I would like to achieve... I'd like to convert the function from VBA to VB.Net and maintain the same functionality of the code below.

Thanks

Sub MySub()
    If MyAddIn(InputBox("Enter an integer: ", "MyAddIn Test")) Then
        Call MyEvenSub
    Else
        Call MyOddSub
    End If
End Sub

Sub MyEvenSub()
    MsgBox "You entered an EVEN number"
End Sub

Sub MyOddSub()
    MsgBox "You entered an ODD number"
End Sub

Function MyAddIn(iInt As Integer) As Boolean
    MyAddIn = False
    If iInt Mod 2 = 0 Then MyAddIn = True
End Function

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39949514
Oops.  When I posted that comment, I'd forgotten that this question was in the Word zone.
0
 
LVL 18

Accepted Solution

by:
Dennis Aries earned 500 total points
ID: 39949586
In order to pass information, you have to use arguments (to pass information to the function) and/or a function to give information back.

Function ImportData(arg As Integer) As Boolean
  ' some code here
  
  ' set the desired returnvalue
  ImportData = (arg Mod 2 = 0)
End Function

Open in new window


And from VBA
If (addIn.ImportData(14)) Then
  ' Do Something
Else
  ' Do Something Else
End If

Open in new window

0
 
LVL 15

Author Comment

by:DrTribos
ID: 39949653
Hi Dennis

Thank you.  My problem is setting up the function in VB.Net so that it is properly exposed to VBA.

A Sub in VB.Net won't let me return a value (as far as I can tell) and changing the word "Sub" to "Function" results in errors.

I can, however, call functions in VBA from VB.Net without any problems.
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39949664
Holy cow it worked! Thank you Dennis - it was just my dumb syntax

as far as I can tell I spent 3 days missing a set of brackets
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39949665
aikimark - thank you also for your help and dual roles, Cheers :-)
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question