Solved

New to Class - VB.net

Posted on 2008-06-16
14
204 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
 
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 125 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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 …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now