CODEWITHSUNDEEP
vb.net
anonymous21
anonymous21

Reputation: 81

How to access variables declared in one sub in another sub in vb.net

In the following code, I want to access ExcelWorkbook1 declared in Sub OpenExcel in Sub SelectRangeOfCells. Please help me with the same.

Public Sub OpenExcel(ByVal Path As String, ByVal Filename As String)
    Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

    xlApp.Visible = True
    If xlApp Is Nothing Then
        MessageBox.Show("Excel is not properly installed!!")

    End If
    Dim MyFile As String = Dir$(Path + "\" + Filename)
    If MyFile = Filename Then
        Dim ExcelWorkbook1 As Workbook = xlApp.Workbooks.Open(Path + "\" + Filename)
    Else
        MessageBox.Show("Excel not found!")
    End If
End Sub

Public Sub SelectRangeOfCells(ByVal Sheet As String, ByVal Range As String)
    ExcelWorkbook1.Worksheets(Sheet).activate
End Sub

This shows error in SelectRangeOfCells Sub that ExcelWorkbook1 is not declared.

Upvotes: 0

Views: 6490

Answers (2)

feeeper
feeeper

Reputation: 3017

You have to declare ExcelWorkbook1 as a class property then use it in yours methods:

Property ExcelWorkbook1 As Workbook

Public Sub OpenExcel(ByVal Path As String, ByVal Filename As String)
    Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

    xlApp.Visible = True
    If xlApp Is Nothing Then
        MessageBox.Show("Excel is not properly installed!!")
    End If
    Dim MyFile As String = Dir$(Path + "\" + Filename)
    If MyFile = Filename Then
        ExcelWorkbook1 = xlApp.Workbooks.Open(Path + "\" + Filename)
    Else
        MessageBox.Show("Excel not found!")
    End If
End Sub

Public Sub SelectRangeOfCells(ByVal Sheet As String, ByVal Range As String)
    ExcelWorkbook1.Worksheets(Sheet).activate
End Sub

More about properties in VB.NET you can learn from MSDN

Upvotes: 1

Aethan
Aethan

Reputation: 1985

This is kinda basic.

Of course, you cannot access ExcelWorkbook1 from another sub because it is declared locally in the OpenExcel sub.

To access ExceWorkBook1 using both subs, you must declare it outside of those two subs.

Dim ExcelWorkbook1 As Workbook 'put this here

Public Sub OpenExcel(ByVal Path As String, ByVal Filename As String)
Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

xlApp.Visible = True
If xlApp Is Nothing Then
    MessageBox.Show("Excel is not properly installed!!")

End If
Dim MyFile As String = Dir$(Path + "\" + Filename)
If MyFile = Filename Then
    ExcelWorkbook1 = xlApp.Workbooks.Open(Path + "\" + Filename) 'The declaration here is moved outside of the sub, granting access to the other subs
Else
    MessageBox.Show("Excel not found!")
End If
End Sub

Upvotes: 1

Related Questions