Share variables between excel and visual basic

Question

I recently started programming with .NET visual basic using visual basic studio. I am also using excel VBA to make some macros. I would be very appreciative if someone could answer a question I have, apologies if the answer is obvious, I'm just getting started:

Basically, if I have set a variable in excel VBA, for example:

dim text as string

text = "hello world"

Would it be possible for me to use that variable when programming in visual basic and have it retain its value from when it was set in the excel VBA macro.

Please comment if you need clarification.

Many thanks.

SOLUTION:

Okay I managed to figure it out with the help of the solutions, the code that works in VB is as follows:

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oxl As excel.application
        Dim owb As excel.workbook
        Dim osheet As Excel.worksheet
        Dim orng As excel.Range
        Dim strtext As String



        oxl = CreateObject("Excel.application")
        owb = oxl.Workbooks.Open(Filename:="C:\Users\USERNAME\Documents\Variable Passing Test.xlsm")

        oxl.Run("dosomethingtostrtext")

        strtext = oxl.Run("getstrtext")

        MsgBox(strtext)

    End Sub
End Class

Passing values is always a possibility, but how you'll be doing it depends on what exactly you want to do with the values. Are two programs running side-by-side, passing values between each other and running in lock step? Or is the one running and storing some output somewhere for the other to pick up whenever it gets run next time? And do they have to run separately, or can you just execute your VBA macro from within your .NET application? — Mikegrann
– Mikegrann, Commented Aug 10, 2016 at 18:50

Busy · Accepted Answer · 2016-08-11 00:24:17Z

1

One way to access variables in another workbook's VBA code is to create functions that return those values to you. Then call those functions from your other app.

For example, let this be code in a module in your Excel file with the macro:

Option Explicit

Private strText As String

' Say you have a routine that manipulates strText (or not, even!)
Public Sub doSomethingToSTRTEXT()
    strText = "Hello World!"
End Sub

' This is the function to call to retrieve strText
Public Function getSTRTEXT() As String
    getSTRTEXT = strText
End Function

And this is code in a VBA project elsewhere (not running .Net on this machine, just microsoft office sad) where you have this:

Option Explicit

Sub Test()
    ' Declare
    Dim WBK As Workbook
    ' Open the workbook in question
    Set WBK = Workbooks.Open(Filename:="C:\Path\To\File\With\VBA.xls")

    ' This code's own variable
    Dim strText As String

    ' Call the routine (or not) that does something to that workbook's vba's strText
    Application.Run (WBK.Name & "!doSomethingToSTRTEXT")

    ' Now let's retrieve that value via function!
    strText = Application.Run(WBK.Name & "!getSTRTEXT")

    ' Show it to me
    MsgBox strText
End Sub

I would like to leave the conversion of the code right above ^ into VB.Net to you (or do a search here on SO for .Net code to handle Excel objects) and try it out

answered Aug 11, 2016 at 0:24

Busy

1386 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ruthus99 Over a year ago

Thanks so much for the reply, I managed to get it to work after some tinkering. This gives me a basic idea of how variables can be transferred and how to manage excel from VB. I will edit my answer and post the code for other lost people like me. Thanks Again Much Appreciated!!!

Busy Over a year ago

I'm glad this helped! Especially glad that you updated your post to showcase the actual VB.Net code solution.

Collectives™ on Stack Overflow

Share variables between excel and visual basic

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related