Solved

New to Class - VB.net

Posted on 2008-06-16
14
208 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Put window Form inside tab page 10 33
VB 2005 how to get a new line of data each time I try to write to a file. 10 24
Help with exporting to excel 4 28
location of a form 2 14
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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