DrTribos
asked on
Pass Variable between VBA and VB.net
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
And...
Thanks in advance
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
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
Thanks in advance
ASKER
Hi Aikimark,
Thanks for stopping by, answers in order:
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 VBA02.
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?)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
ASKER
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,
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,
ASKER
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
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
Oops. When I posted that comment, I'd forgotten that this question was in the Word zone.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
as far as I can tell I spent 3 days missing a set of brackets
ASKER
aikimark - thank you also for your help and dual roles, Cheers :-)
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?