Solved

New to Class - VB.net

Posted on 2008-06-16
14
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

751 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