Excel VBA: problem passing workbook, worksheet objects to subroutines

Question

I have a main routine that creates a new Workbook and dynamically associates new sheets with it (based on the data in a separate input workbook/sheet).

Excerpting the main routine:

Sub MainRoutine()
    Dim NmOutBook As String
    NmOutBook = "Client1Output_" & Format(CStr(Now), "yyyy_mm_dd_hh_mm")

    Dim PosSourceBk, TrnSourceBk, OutputBk As Workbook
    Set PosSourceBk = Workbooks.Open("U:\Documents\Implementations\Client1\Client1Positions.xlsx")
    Set TrnSourceBk = Workbooks.Open("U:\Documents\Implementations\Client1\TradeHistory_0301.xlsx")
    Dim TrnSrcSht, TrnOutSht, PriorTrnOutSht, PosOutSht As Worksheet
    Set TrnSrcSht = TrnSourceBk.ActiveSheet

    'Create workbook to store output sheets
    Set OutputBk = Workbooks.Add

...

If (SecNm <> PriorSecNm) Then
    Set TrnOutSht = OutputBk.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    TrnOutShtName = CStr(SecNm) + "_b"
    TrnOutSht.Name = TrnOutShtName
    AddXactSheetHeaders OutputBk, TrnOutSht

I'm passing my output workbook object and the corresponding worksheet object to the subroutine AddXactSheetHeaders, which looks like this:

Sub AddXactSheetHeaders(ByVal wb, ByVal ws)
    With wb
        With ws
            Range("A1").Value = "TradeDate"
            Range("B1").Value = "SettleDate"
            Range("C1").Value = "Tran ID"
            Range("D1").Value = "Tranx Type"
            Range("E1").Value = "Security Type"
            Range("F1").Value = "Security ID"
            Range("G1").Value = "SymbolDescription"
            Range("H1").Value = "Local Amount"
            Range("I1").Value = "Book Amount"
            Range("J1").Value = "MOIC Label"
            Range("K1").Value = "Quantity"
            Range("L1").Value = "Price"
            Range("M1").Value = "CurrencyCode"
        End With
    End With
End Sub

The result of this is that cells A1:M1 are updated, but in the wrong workbook / worksheet.

I've tried passing the object references by reference; when I do this a different (but still wrong) workbook / worksheet is updated.

I must be missing something obvious, but have no idea what it might be.

Any assistance appreciated. Thanks.

Answer 1

Use ByRef to pass the workbook/worksheet objects by pointer instead of copying them then use them as parent references in the helper sub. To reference a Range inside a With ... End With, use a prefix period.

Sub AddXactSheetHeaders(Byref wb as workbook, ByRef ws as worksheet)

  With wb
   With ws
     .Range("A1").Value = "TradeDate"
     .Range("B1").Value = "SettleDate"
     .Range("C1").Value = "Tran ID"
     .Range("D1").Value = "Tranx Type"
     .Range("E1").Value = "Security Type"
     .Range("F1").Value = "Security ID"
     .Range("G1").Value = "SymbolDescription"
     .Range("H1").Value = "Local Amount"
     .Range("I1").Value = "Book Amount"
     .Range("J1").Value = "MOIC Label"
     .Range("K1").Value = "Quantity"
     .Range("L1").Value = "Price"
     .Range("M1").Value = "CurrencyCode"
   End With
  End With 

End Sub

In fact, the wb reference is completely unnecessary and syntactically wrong. The ws worksheet knows what its parent workbook is.

Sub AddXactSheetHeaders(ByRef ws as worksheet)

  With ws
     .Range("A1").Value = "TradeDate"
     .Range("B1").Value = "SettleDate"
     .Range("C1").Value = "Tran ID"
     .Range("D1").Value = "Tranx Type"
     .Range("E1").Value = "Security Type"
     .Range("F1").Value = "Security ID"
     .Range("G1").Value = "SymbolDescription"
     .Range("H1").Value = "Local Amount"
     .Range("I1").Value = "Book Amount"
     .Range("J1").Value = "MOIC Label"
     .Range("K1").Value = "Quantity"
     .Range("L1").Value = "Price"
     .Range("M1").Value = "CurrencyCode"
  End With 

End Sub

Answer 2

This is because inside your With Segment, you are not using a dot (.) to specify the change in that wb/ws.

Inside any With segments, using a dot, ties the "command" to the With Segment. Ej:

With ThisWorkbook.Sheets("Sheet5")
    Range("A5").Value = "String Test" 'This changes the Cell A5 of the ActiveSheet
    .Range("A5").Value = "Test 2" 'This Changes the Value of Sheet5, part of the With Segment
End With

Just add the Dots/Periods

Sub AddXactSheetHeaders(ByVal wb, ByVal ws)
    With wb
        With ws
            .Range("A1").Value = "TradeDate"
            .Range("B1").Value = "SettleDate"
            .Range("C1").Value = "Tran ID"
            .Range("D1").Value = "Tranx Type"
            .Range("E1").Value = "Security Type"
            .Range("F1").Value = "Security ID"
            .Range("G1").Value = "SymbolDescription"
            .Range("H1").Value = "Local Amount"
            .Range("I1").Value = "Book Amount"
            .Range("J1").Value = "MOIC Label"
            .Range("K1").Value = "Quantity"
            .Range("L1").Value = "Price"
            .Range("M1").Value = "CurrencyCode"
        End With
    End With
End Sub