VBA Syntax for Passing Multiple Variables Between Subs

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
I'm having a problem on what I think is a simple issue - passing variables between subs. I have no problem passing a single variable from one sub to another, however when I try to do this with two I get the error "Run-type error '13': Type mismatch. Could someone help me out with correct syntax? I'm trying to get to where I can just pass workbook name variables over.

Here is what I'm trying to use that is failing:

HTML:
Sub OpenProdSchedules()

Dim Template As Workbook
Dim ProdSchedule As Workbook

Set Template = ActiveWorkbook

'Delete Prod Schedule worksheet if it already exists

    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("Prod Schedule").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

'Resume

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Prod Schedule"
    Range("A1").Value = "Job"
    Range("B1").Value = "Item"
    Range("C1").Value = "Description"
    Range("D1").Value = "Scheduled Completion Date"

MsgBox "Please select the Production Schedule."

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the PRODUCTION SCHEDULE file to import", _
FileFilter:="Excel Files *.xl* (*.xl*),")

If FileToOpen = False Then
    MsgBox "No file specified, Cancelling process."

    Exit Sub

    Else

    Workbooks.Open Filename:=FileToOpen

    Set ProdSchedule = ActiveWorkbook

End If


'HERE IS WHERE THE CODE ERRORS
Call CopyProdData(Template, ProdSchedule)

End Sub
HTML:
Sub CopyProdData(Template As Worksheet, ProdSchedule As Worksheet)

Dim StartRow As Long

StartRow = Range("A1048576").End(xlUp).Row + 1

Template.Activate

End Sub

Thanks for your help
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
The reason for the type mismatch error in your code is because you declare your variables as workbooks, but your function only takes worksheets.

If you change this line:

Code:
Call CopyProdData(Template, ProdSchedule)
into this:

Code:
Call CopyProdData(Template.Sheets("Prod Schedule"), ProdSchedule.Sheets("Prod Schedule"))
it might work, although I am not sure that is what you intend to do...
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
That did it, thanks! I had incorrectly tried to define the variable type again and that must have been causing the issue. Removing it and going your route works.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top