How To Program Excel VBA code using VB.Net???

[sankartalam]

Can anyone help me please,
I have installed Microsoft Visual Studios 2008 on my machine and trying to working on the Excel VBA code using VB.Net. I am not able to initialize the excel object.
The Following are the steps i followed to do that...
a. I created the project using the class library.(I selected my type of the project as class library)
b. In which i have created the COM Class(which by default had one sub routine in it). and have created one Sub routine.
c. The SUb Routine which I have created have do some operations in the Excel sheet. Those are
i. The subroutine has to find out last empty column
ii. It has to select some Range of cells and has to format those range with the conditions.
iii. It has to do some operations by using some loops (for , while) and has to check some conditions using IF conditions.

after all these i will build my project which will create some DLL file which i will use to call in excel VBA code.

The main issue here is while building there is no issue, i haven't get any build error.
But when I am calling the SubRoutine which I wrote in the COM class was not initialising the Excel Obeject.
Could anyone please help me to get the solution???
Thanks Inadvance
Sankar

[Kenneth Hobs]

This is a vba forum so vb.net questions are not what members expect to see. There has been talk of adding that type of subforum to this site. I don't know of any other Excel sites that has a vb.net subforum either.

Sites like vbcity.com and vbforums.com might have more members that have done what you want to do.

With all this said, Chip Pearson's web site has some of best tips for vb.net for Excel. Do a google search and search for:
vb.net site:http://www.cpearson.com
The first link will probably be http://www.cpearson.com/excel/CreatingNETFunctionLib.aspx

At the link above, notice that Chip explains Regasm.exe. This would be used rather than regsvr32.exe. However, I found that a reference in vba to the tlb file was the best route. Regasm.exe and another utility in the framework files can be used to create your tlb.

If the links do not help, post back. I can lookup the simple vb.net project that I did a while back. Posting your code helps us help you too. However, I think Chip's tips will get you up to speed.

[JWhite]

I've just done my first big project to convert VBA code to VB.Net. Sounds like you may be doing an add-in, too, so you'll have a class of ThisAddin which handles the startup.

The Excel application is "me.application". You can do something like:

Dim xapp as Excel.Application = me.Application

You have to do that in the ThisAddin class. "me.Application" is not recognized outside that class. I also ran into the problem that after instantiating "xapp" in ThisAddin I couldn't reference it outside of that class even if I made it a Public variable. However, public variables declared in modules CAN be referenced in all other modules. So I passed "me.Application" to a module and declared it there. In other words, in the ThisAddin class Startup routine, I called a subroutine in a module like this:

SetApp(me.Application)

And in the module I did something like this:

Public xapp as Excel.Application
Sub SetApp(ByVal ExcelApp as Excel.Application)
xapp = ExcelApp
End Sub

It seemed like a weird way to do it but I couldn't find a reference to help me and I just worked this out by trial and error. There MUST be a better way but it's working for me. By the way, if you haven't bought "Visual Studio Tools for Office" by Eric Carter and Eric Lippert, save yourself a lot of grief and get it now. It is an absolutely required reference for using VB.Net with Office. It didn't answer THIS question but it did answer most of them.

[Kenneth Hobs]

This is what I did for my AssemblyInfo.vb:

Imports System.Reflection
Imports System.Runtime.CompilerServices
Imports System.Runtime.InteropServices

' General Information about an assembly is controlled through the following
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.


' TODO: Review the values of the assembly attributes


<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyCompany("RMCC")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

' Version information for an assembly consists of the following four values:

' Major version
' Minor Version
' Build Number
' Revision

' You can specify all the values or you can default the Build and Revision Numbers
' by using the '*' as shown below:

<Assembly: AssemblyVersion("1.0.*")>

For my TestClass.vb:

Option Strict Off
Option Explicit On
<System.Runtime.InteropServices.ProgId("TestClass_NET.TestClass")> Public Class TestClass

Public Function SquareMe(ByVal X As Single) As Single
SquareMe = X * X
End Function
End Class

For my vba code to test the dll after setting the reference to the TestClass.tlb:
' Execute the MyTestServer.TestClass.SquareMe method.
Public Function CallSquareMe(ByVal v As Single) As Single
Dim obj As MyTestServer.TestClass
Set obj = New MyTestServer.TestClass
CallSquareMe = obj.SquareMe(v)
End Function

Sub Test()
MsgBox "3^2=" & CallSquareMe(3)
End Sub
Obviously, the Function can be used as a UDF:

'=CallSquareMe(A1) 'If A1=3 then result is 9.
Be sure to make your .net project COM visible in the vb.net project: Right click the Project > Properties > Application > Assembly Information... and set Com Visible.

The paths for vb.net 2008 will be different. Here is what I use in a tlbexp.bat file to make a tlb file using vb.net 2005:

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\tlbexp.exe" %1 %2
Another way, regasm.bat to make a tlb file:

"C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm.exe" %1 %2 %3
My sn.bat:

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" %1 %2
My gacutil.bat:

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" %1 %2
Here is how I use the bat files for x.dll at a DOS prompt or Win+R, Start > Run:

regsvr32 x.dll
tlbexp x.dll /out:x.tlb
sn -i x.snk
regasm x.dll /tlb /codebase
gacutil /i x.dll
Here are some general notes about strong names.
Strong Names are not always needed:
1. Use sn.exe to generate an SNK, strong name, file. Add it by right click Project >
Properties > Signing > Sign the assembly and set the SNK file generated earlier.

or

2. By Code example class.vb method:

Imports System
Imports System.Reflection
<Assembly: AssemblyKeyFile("..\TestProj.snk")>
<Microsoft.VisualBasic.ComClass()> Public Class COMClass1
' <ComClass(COMClass1.ClassId, COMClass1.InterfaceId, COMClass1.EventsId)> _

#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "6DB79AF2-F661-44AC-8458-62B06BFDD9E4"
Public Const InterfaceId As String = "EDED909C-9271-4670-BA32-109AE917B1D7"
Public Const EventsId As String = "17C731B8-CE61-4B5F-B114-10F3E46153AC"
#End Region
' A creatable COM class must have a Public Sub New()
' without parameters. Otherwise, the class will not be
' registered in the COM registry and cannot be created
' through CreateObject.
Public Sub New()
MyBase.New()
End Sub

Public Function myFunction() As Integer
Return 100
End Function
End Class

[sankartalam]

Thank you very much for your reply Mr. Kenneth Hobs.

The Project code that you posted can work for the non excel operatrions. But it is different in the case of Excel. There it needs Excel Object Initilization. There I have an issue.
I have added the following references
office object library 12.0
microsoft excel object library 10.0 and 5.0
and started developing my sub routine. In which i haven't passed any parameters and inside of that sub routine
I have declared the variables as follows
xlApp as Excel.Application
xlWB as Excel.WorkBook
xlSheet as Excel.WorkSheet

and when I am trying to initialize the above variables its giving me the exception.
I have checked so many examples on the same
the initialization is as follows
xlApp = new Excel.Application
But for me I am not able to initialize as above instead
xlApp = new Excel._Application i can do.
Here its saying some error like interface cant be initialized.


I Hope the way JWhite did will help some how.

Will Start working on my project in JWhite way.

[sankartalam]

Thank you very much for the reply JWhite.

Could you please post the COM class along with the sub routine code.
The main issue I am getting is you have understood very well. So Hopefully you can help me. Now will start working on my project in your way of initialization. it would be more helpfull if you can post the subroutine code along with your COM class.

[Kenneth Hobs]

Maybe something along the lines of what I am did in this MSWord vba code would help. It automates excel from msword's vba. The concepts should be similar in .net.

Sub XLS(xlsFile As String)
Dim xlApp As Excel.Application 'Early Binding
'Dim xlApp As Object 'Late Binding
Dim xlsheet As Excel.Worksheet
Dim xlbook As Excel.Workbook
Dim r As Excel.Range

'On Error GoTo theEnd
Set xlApp = CreateObject("excel.application") 'Better method
'Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.ScreenUpdating = False
'Next fails if xlsFile does not exist.
Set xlbook = xlApp.Workbooks.Open(xlsFile)
Set xlsheet = xlbook.Worksheets("Sheet1")
With xlsheet
.Range("A1").Value = UserForm1.TextBox1.Value
.Range("A2").Value = UserForm1.TextBox2.Value
.Range("A3").Value = UserForm1.TextBox3.Value
End With

'On Error GoTo theEnd
xlbook.Save
xlbook.Close False

theEnd:
On Error Resume Next
Set xlsheet = Nothing
Set xlbook = Nothing
xlApp.Quit '<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set xlApp = Nothing

'Shell "cmd /c " & """" & xlsfile & """"
End Sub
The trouble with early binding is that you are limited to what version of Excel it will work with. Sometimes, I use early binding to help me code and then use late binding for the production version.

[JWhite]

I'm glad that my post helped. I think the problem is trivial for experienced Visual Basic programmers. As I said, I think there are better or more sophisticated ways to do this. You asked for my COM class code. As I said, my application is an add-in which creates a class called Thisaddin which handles the application start-up. Here is the minimum code to instantiate the Excel application:

Public Class ThisAddIn

Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
ModInitialize(Me.Application)
End Sub

And then in another module we'll call StartApp you have:

Module StartApp
Public xapp As Excel.Application
Sub ModInitialize(ByVal ExcelApp As Excel.Application)
xapp = ExcelApp
End Sub

As I said before, by declaring "xapp" as a public variable in a module, I am able to use it in all modules and classes. If you declare it in a class, I found that I could only use it in that class.

I hope this does the job for you. I'm fighting deadlines at a small software start-up so probably won't have time to look at the forum for a while.