Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

New to Class - VB.net

Posted on 2008-06-16
14
Medium Priority
?
231 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:VBdotnet2005
  • 6
  • 5
  • 3
14 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21798332
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

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21800401
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
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21802420
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 48

Expert Comment

by:jpaulino
ID: 21802474
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.
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 500 total points
ID: 21803366
You could add it to a class and implement it as a shared method. This does much the same as you'd expect a module to do, but with better encapsulation and intellisense.


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
 
Public Class MyExcel
    Public Shared Function ReadExcel(ByVal SourcePath As String, ByVal StartRowToRead As Integer) As String
        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 myVal as String
            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
Return myVal
       Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try
    End Function
End Class

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21803625
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!

0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21803837
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.
0
 

Author Comment

by:VBdotnet2005
ID: 21804291
Warning      1      Variable 'myval' is used before it has been assigned a value. A null reference exception could result at runtime.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21804325
Change it to:

Dim myVal as String = String.Empty

But thats is just an warning!
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21804343
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

0
 

Author Comment

by:VBdotnet2005
ID: 21804419
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.
0
 

Author Comment

by:VBdotnet2005
ID: 21804477
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
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21804486
Replace:
   MessageBox.Show(ex.Message)

With:
   Return String.Empty
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21804503
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question