asked on

New to Class - VB.net

I am just trying to understand how to create a class. Below is my sample.
How can I get my varialbe "myval" and display value in a text box or a variable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

textbox1.text = ???

or

dim mysTring as string = myVal???

End Sub

Imports Microsoft.Office.Interop

Public Class MyExcel

Dim myVal As String

Public Sub ReadExcel(ByVal SourcePath As String, ByVal StartRowToRead As Integer)

Dim xlApp As New Excel.Application
Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open(SourcePath)
Dim wrksheet As Excel.Worksheet = wrkbook.Worksheets(1)

Try

Dim I As Integer = StartRowToRead
Dim re As Boolean

While re = False

I += 1

myVal = Trim(CStr(xlApp.Range("A" & I).Value))

If CStr(xlApp.Range("A" & I).Value) = Nothing Then

re = True

End If

End While

wrkbook.Close()
wrkbook = Nothing
xlApp = Nothing

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

GC.Collect()
GC.WaitForPendingFinalizers()

End Try

End Sub

End Class

Jeff Certain

What you're looking for is how to "instantiate" a class.

I've added "test" before the result, since your current code won't have anything in myVal.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
  Dim this as New MyExcel
  textbox1.text = "test" & this.myVal
End Sub

Open in new window

Jorge Paulino

You don't need a class for this! You can create a public function, in a module, and retrieve the value without creating a new instance.

Example:

Public Function ReadExcel(ByVal SourcePath As String, ByVal StartRowToRead As Integer) As String
Dim myVal As String = String.Empty
' your code
myVal = Trim(CStr(xlApp.Range("A" & I).Value))
' your code
Return myVal ' in the end
End Function

Then you can use in your forms:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Textbox1.text = ReadExcel(SourcePath, StartRowToRead)
End If

Jeff Certain

Modules aren't the solution for everything.

In general, they break OO encapsulation and encourage hard-to-maintain code, since it can be very difficult to find where code is located, intuit what it does, etc.

The tendency with modules is to allow them to grow out-of-control, since they're a convenient place to dump stuff and there's no expectation of a single responsibility within a module.

I've seen "helper classes" grow to be 4000 lines of code. Bad ju-ju there.

Jorge Paulino

Chaosian I agree with you about the module but do you think a function like this should stay in a class and create a new instance everytime you need to retrieve a value ?

How do you use it ?

Dim this As new MyExcel
this.ReadExcel() ' to fill the variable
textbox1.text = this.myVal

Personally I don't think its necessary.

ASKER CERTIFIED SOLUTION

Jeff Certain

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jorge Paulino

Ok, but thats's almost the same: change to function with return parameter and public/share (using shared).

But both methods should work fine.

@VBdotnet2005

Don't forget to move this:
Dim xlApp As New Excel.Application
Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open(SourcePath)
Dim wrksheet As Excel.Worksheet = wrkbook.Worksheets(1)

inside the Try ... catch method!

Jeff Certain

Well, it's almost the same, but it's more likely to end up in a class that has limited responsibility instead of a module that's just ugly.

Whenever appropriate, I prefer to use extension methods anyhow.

VBdotnet2005

ASKER

Warning 1 Variable 'myval' is used before it has been assigned a value. A null reference exception could result at runtime.

Jorge Paulino

Change it to:

Dim myVal as String = String.Empty

But thats is just an warning!

Jeff Certain

That warning isn't really an issue, since strings are initialized to an empty string.

If you want to make the warning disappear, change Dim myVal as String to Dim myVal As String = String.Empty

VBdotnet2005

ASKER

Sorry, Here is another warning

Warning 1 Function 'ReadExcelbyColumn' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.

VBdotnet2005

ASKER

Access of shared member, constant member, enum member or nested type through an instance; qualifying expression will not be evaluated.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim myEx As New myExcel

Me.TextBox1.Text = myEx.ReadExcelbyColumn("mypath", 1)

End Sub

Jorge Paulino

Replace:
MessageBox.Show(ex.Message)

With:
Return String.Empty

Jeff Certain

Shared methods don't need you to instantiate the calss.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
textbox1.text = "test" & MyExcel.ReadExcel("param1", "param2")
End Sub