VB for Excel,working with subroutines(modules)

[Norah]

I need to introduce several subroutines (modules ,not functions) in a VB main program(code). The main code and the subroutines use several sheets of an Excel file.

I introduce in the main code the Excel file with the following code:

option explicit
public hoja as object
public sub command1_click()
dim hoja as object
set hoja=createobject("Excel.Sheet")
hoja.application.visible=true

with commondialog1
.cancelerror=true
.filter="sicrosoft excel files(*.xls)¦*.xls"
.dialogtitle="select an excel file for open"
.showopen
end with
hoja.application.workbooks.open commondialog1.filename

My question is : Although I know how to transfer parameters from the main to the subroutines, I do not know how to transfer the Excel file.

Any help? Thanks.

[Ecniv]

option explicit
 
public hoja as object
public objWrkBk as object
 
public sub command1_click()
  dim hoja as object
  
  set hoja=createobject("Excel.Sheet")
  hoja.application.visible=true
 
  with commondialog1
    .cancelerror=true
    .filter="sicrosoft excel files(*.xls)¦*.xls"
    .dialogtitle="select an excel file for open"
    .showopen
  end with
  if len(commondialog1.filename)>0 then
    [b]set objWrkBk=[/b] hoja.application.workbooks.open(commondialog1.filename)
  end if
end sub
 
 
public sub whatever(byref objSht as object)
'---- objSht is an object in this case its the Excel sheet you opened.
end sub

Something like that?


Vince

[BrianB]

1. Firstly you seem to be using Visual Basic. This forum is for VBA *Visual Basic for Applications* which is contained in Microsoft Office. You might be better posting your messages in the VB forum in future.

2. Generally there is no need to "pass parameters" to subroutines. The alternative is User Defined Functions which require parameters to *return a calculated value*. In the example below variables are declared at (top of) Module Level which makes the variables available to all subroutines in the module. To make variables available to all *modules* declare them as Public.

3. The MS Office object heirarchy for Excel is Application.WorkBook().WorkSheet(). This seems to have been lost in your code, and is probably one of the reasons for your problems. Your code deals with new, empty, workbooks and worksheets whereas you will probably need to access existing ones. The Excel "file" is a Workbook. The following code is an example of how to do this. This is not definitive. There are numerous variations possible. I have tried to show that there is more than one method. I do not know if this will actually work using Visual Basic. It works OK using VBA :-

'=====================================
'- EXCEL APPLICATION EXAMPLE
'======================================
'-
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook    ' not always necessary
Dim xlsheet As Excel.Worksheet  ' not always necessary
Dim MyPath As String
Dim MyBookName As String
Dim MySheetName As String
'---------------------------------------

'=====================================================
Sub GET_EXCEL_DATA()
    '- set Excel application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    '-------------------------------------------------
    '- open workbook and set worksheet variable
    MyPath = "C:\TEMP\"
    MyBookName = "BOOK1.XLS"
    MySheetName = "SHEET1"
    With xlApp
        .Visible = False
        .Workbooks.Open (MyPath & MyBookName)
        Set xlBook = .Workbooks(MyBookName)
        Set xlsheet = xlBook.Worksheets(MySheetName)
    End With
    '-------------------------------------------------
    '- get sheet data using defined xlsheet object
    MsgBox (xlsheet.Range("A1").Value)
    '-------------------------------------------------
    '- alternative method without defined book\worksheet
    '- get sheet data using defined xlapp and Excel-type code
    MsgBox _
    (xlApp.Workbooks(MyBookName).Worksheets(MySheetName).Range("A1").Value)
    '--------------------------------------------------
    '- close Excel
    xlApp.CutCopyMode = False
    xlApp.Workbooks.Close
    '- release variables
    Set xlsheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Sub
'==============================================