How can I pass string from vba to vb.net?

[ginger0001]

Please give me an easy example code for both vba and vb.net
how can I pass string from vba to vb.net?
I am very new to both.

Thank you so much

 

[Estuardo]

G'd evening Ginger,
Can you tell us what exactly want to accomplish? I mean there are many ways to do what you want but depending on what you need it can become complex. You can open almost any exe from vba. and any form in your .net application can accept arguments, so you can the do something like this:

[B]vba[/B]
VBA.CreateObject("WScript.Shell").Run "c:\YouNetApp.exe"

[B].Net App[/B]
    Private Sub YourForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If My.Application.CommandLineArgs.Count > 0 Then
            MsgBox(My.Application.CommandLineArgs.Item(0))
        End If
    End Sub

I guess that this is not what you're after, but is just for you to see that there are many, many ways to do what you need, but you have to give as much info as you can to make easier to help you.

 

[ginger0001]

I want to take a content from an excel cell by vba
and pass that string to vb.net which the file is .sln
I want to show that string on vb.net by MsgBox

Thank you so much

 

[Estuardo]

Ginger,
If all what you need is to display a msgbox, why don't you do it from Excel itself? or with vbscript or with a bat file or anything else. I mean to build and .net app for a msgbox is a waste.
And by the way sln is a VS solution not an executable.

 

[ginger0001]

I want to design an user interface by vb.net.
At first, I call the excel solver by vb.net
after I got result, I need to design an user interface by vb.net to show the result
That why I should take the data from excel(got from solver) by vba.

 

[Estuardo]

I´m sorry Ginger i don't get it. If you need that your .net app get a value from a excel's cell, then you will need to open excel --> change it's cells value --> and finally open the .net app. it is the flow? if so. I have to be missing something or your design is not the appropriated. Do you want to open your .net app every single time a cell changes? How often that value changes? what about if your .net app is already open? what if more than one user change that value? what if a row or column is added/deleted from your worksheet?
Like i said i must be missing something...
Could you please tell us exactly what's your scenario? what do you have and what do you want to achieve?

 

[ginger0001]

1. I try to connect vb.net and vba by this code
This is my vb.net code
Imports Microsoft.Office.Core Imports Microsoft.Office.Interop




Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Start.Click
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks
Dim oSheets As Excel.Sheets
Dim oSheet As Excel.Worksheet


Try
Dim FirstName As String
FirstName = TextBox1.Text
oExcel = CreateObject("Excel.Application")
oBooks = oExcel.Workbooks
oExcel.Visible = True 'if you want to see it FALSE if not
oBook = oBooks.Open("C:\Documents and Settings\Administrator\Desktop\7typesexcel10032012.xlsm")
DirectCast(DirectCast(oExcel.Workbooks(1).Sheets(1), Excel.Worksheet).Cells(5, "B"), Excel.Range).Value = FirstName
oSheet = oBook.Worksheets(1)
'oSheet.Range(oSheet.Cells(1, 1)).Value = Name
oExcel.Run("Solver.xlam!Autpen")
oExcel.AddIns.Add("C:\Program Files\Microsoft Office\OFFICE12\Library\SOLVER\SOLVER.XLAM")
oExcel.DisplayAlerts = True
Dim tmpIntValue As Integer = 44 'This is the Value that should be the result
oExcel.Run("Sheet8.CommandButton2_Click")
Console.Write(tmpIntValue)










Catch




End Try




End Sub


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


End Sub


Private Sub TextBox1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.DoubleClick
Dim FirstName As String
FirstName = TextBox1.Text
Dim xlsInstance As New Excel.Application()
xlsInstance.Visible = True
xlsInstance.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\7typesexcel10032012.xlsm")
DirectCast(DirectCast(xlsInstance.Workbooks(1).Sheets(1), Excel.Worksheet).Cells(1, "A"), Excel.Range).Value = FirstName
End Sub
End Class

2. This is my vba code
Private Sub CommandButton2_Click()
' Public Sub sub1(ByVal SomeValue As Integer)
Range("C5:L14").ClearContents
Application.Run "Solver.xlam!SolverReset"


SolverOk SetCell:="B17", MaxMinVal:=1, ByChange:="C5:L14"
SolverAdd CellRef:="C18", Relation:=2, FormulaText:="E23"
SolverAdd CellRef:="C19", Relation:=2, FormulaText:="E24"
SolverAdd CellRef:="C20", Relation:=2, FormulaText:="E25"
SolverAdd CellRef:="C21", Relation:=2, FormulaText:="E26"
SolverAdd CellRef:="C22", Relation:=2, FormulaText:="E27"
SolverAdd CellRef:="C23", Relation:=2, FormulaText:="E23"
SolverAdd CellRef:="C24", Relation:=2, FormulaText:="E24"
SolverAdd CellRef:="C25", Relation:=2, FormulaText:="E25"
SolverAdd CellRef:="C26", Relation:=2, FormulaText:="E26"
SolverAdd CellRef:="C27", Relation:=2, FormulaText:="E27"
SolverSolve UserFinish:=True
SolverReset
'finish
End Sub

3. After I got the result from solver which are printed on excel cell, I want to extract all content from cells to vb.net in form of string

 

[Estuardo]

Ginger,
I'm not by far any Excel savvy, but i don't think your automation code is appropriated. What you are trying to do, in my opinion, won't work. You may have the right tools but not applied in the right way.
Consider changing the structure of your project. When you automate a task is to exclude as much as possible human intervention. In your case some one need to click on a button [within Excel] to perform a complex task, then you need to (in some way) send that result to your .net app. Why? The same person who clicked the button can copy/paste that result if is just one cell, right?
Please don't get me wrong. My point is that what you need to do, is something that can be done in some other/better way.
Let's say that finally you got to "pass" that value to your .net app. Then what? what are you gonna do with that value? do this value needs further manipulation? Do you see what i mean?
With VS 2k10 you can connect directly to your workbook/worksheet using the pre-built templates from the MS Office Menu Project Templates. This will help you avoid automation that in most cases is a pain.