anonymous21 anonymous21 - 2 months ago 9
Vb.net Question

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.

Answer

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
Comments