VBA and Excel, help passing a worksheet to a subroutine

[Alessandro]

Hi,

I'm passing a worksheet object to a sub but I get this error: "Type not
corresponding for argument ByRef" (message could be different in English
because translated from Italian, anyway this is the meaning).

This is the code calling Sub Checker, I don't know where the error could be
.... "objWorksheet" is a Worksheet object and also ByRef is the default.

//START

Private Sub Main
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)


Checker objWorksheet, CDate(RangeForm.FromTextBox),
CDate(RangeForm.ToTextBox), "Bank", 6, 2, 3
End Sub

Private Sub Checker(objWorksheet As Worksheet, fromRange As Date, toRange As
Date, sheetName As String, codSpesaCol As Integer, dataCol As Integer,
spesaCol As Integer)

//do something

End Sub

//END


Do you have any idea about the problem ?

Many thanks in advance,
Best regards

Alessandro

 

[Mourad Louha]

Hi Alessandro...

how are declared the var's? I tried following code in Excel 2003
and it works on my computer. Perhaps this helps you.

Private objExcel As Object
Private objWorkbook As Workbook
Private objWorksheet As Worksheet

Private Sub Main()

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

' Checker objWorksheet, CDate(RangeForm.FromTextBox),
CDate(RangeForm.ToTextBox), "Bank", 6, 2, 3
Checker objWorksheet, CDate("1"), CDate("1"), "Bank", 6, 2, 3

MsgBox "Close"

Set objWorksheet = Nothing
Set objWorkbook = Nothing

objExcel.Quit

Set objExcel = Nothing

End Sub

Private Sub Checker(objWorksheet As Worksheet, fromRange As Date, _
toRange As Date, sheetName As String, _
codSpesaCol As Integer, dataCol As Integer, _
spesaCol As Integer)

MsgBox objWorksheet.Name

End Sub

Public Sub Test()

Main

End Sub

Regards