Question: Passing objects to sub-procedures

[werafa]

Hi all,
I'm scratching my head on this one, and hope someone can say why it is so.

I have always (in the past) passed objects and variables to sub-procedures using this syntax

Sub Master()
dim myWB as workbook

   DoSomethingSub(myWB)
   more code etc.
end sub

Sub DoSomethingSub(sourceWB as workbook)
.....
End Sub

This is failing in the project I am currently working on (object doesn't support this property or method), and I am being forced to write the calls with the syntax:

DoSomethingSub SourceWB:=MyWB

which works fine

Does anyone know why this should be so?
the actual code is pasted below, and the procedure calls in 'CloseSrcWB' work fine

Thanks in advance
Werafa

Public Sub UpdateData()
Dim sArr(1 To 2) As String
Dim lLoop As Long
Dim sourceRange As Range
Dim sourceWB As Workbook
Dim arrSourceData() As Variant

    sArr(1) = "MCG"
    sArr(2) = "FNW"

    DeleteOldData
    
    'load new data
    For lLoop = 1 To 2
        Set sourceRange = OpenSourceData(sArr(lLoop))
        arrSourceData = SelectSourceData(sourceRange)
        Set sourceWB = sourceRange.Parent.Parent
        CloseSrcWB myWB:=sourceWB
        WriteDataToTable dataArray:=arrSourceData
    Next lLoop
End Sub


Public Sub CloseSrcWB(myWB As Workbook)
' ----------------------------------------------------------------
' Procedure Name: CloseSrcWB
' Purpose: Close Source data WB and reset application environment parameters
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter sourceWB (Workbook): WB to be closed
' ----------------------------------------------------------------
    Call SilentClose(myWB)
    Call SilentOpen(False)
End Sub

[Paul_Hossler]

I have always (in the past) passed objects and variables to sub-procedures using this syntax

I believe that it depends on whether it's object(s) and number of parameters

This should work

Call CloseSrcWB (sourceWB)

This is a combination of options if you're interested

Option Explicit

Sub Master()
    Dim myWB As Workbook
    
    Set myWB = ThisWorkbook
    
    'pass objects
    Call DoSomethingSub(myWB)
    DoSomethingSub myWB
'    DoSomethingSub (myWB)  <<<<<<<<<<<<<< Err 438


    'pass not-objects, one parameter
    DoSomethingElse 10
    DoSomethingElse (10)


    'pass not-objects, more than one parameter
    DoSomethingElse2 10, 5
    Call DoSomethingElse2(10, 3)
    
    'doesn't work
    'DoSomethingElse2 (10, 3)    '   <<<<<<<<<<<<<< syntax error
End Sub


Sub DoSomethingSub(sourceWB As Workbook)
    MsgBox sourceWB.FullName
End Sub


Sub DoSomethingElse(N As Long)
    MsgBox 2 * N
End Sub


Sub DoSomethingElse2(N1 As Long, N2 As Long)
    MsgBox N1 * N2
End Sub

[werafa]

Thanks Paul,

I have used this (mainly to improve code readability), but have never understood the difference between the three call methods, or the reason that you would choose one over the other. This does explain why my code is suddenly breaking though.

Do you know of a good info resource that covers this topic?
Werafa

[Paul_Hossler]

Online help for 'Call'

https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue



From another forum, some information, but a lot of personal opinions

https://stackoverflow.com/questions/...word-in-vb-vba

[Jan Karel Pieterse]

The crux is in the use of brackets (or not!). In VBA, you are obliged to use brackets when:

- You use

Call Someroutine(SomeArgument)

- You call a function and assign the result:

SomeVariable = SomeFunction(SomeArgument)

You are supposed to omit the brackets when you call a routine without using Call:

Someroutine SomeArgument

If you do not omit the parentheses in this final example, VBA first evaluates the expression between parentheses (in case of an object: using the default property if none is specified!) before passing it to the routine.

[werafa]

Thanks all,

It would seem that from the lack of clear guidance that this may be a legacy issue.

The one thing that stands out is that enclosing individual (and single) parameters in brackets will enforce a 'byval' situation - and that this will cause an error when passing an object - and this would seem to be the cause of the issue that prompted my question.

lessons are:
> If you 'Call SomeFunction', you discard the return value
> using 'childVar:=parentVar' aids readability, and allows the sorting of variables into any order
> the main value of 'Call' is an improvement in readability - but this usually can not be applied to functions, and most coders opt for consistency when calling subs and functions

Other than this, there appears to be no particular best-practice or body of knowledge to support any particular opinion.

Werafa

[Jan Karel Pieterse]

It is not just a "byVal" situation which counts with objects. What counts is that the argument in parntheses is first EVALUATED. If you pass a range variable that way, you are not passing the object, but the value(s) of the range, hence the result of the example below:

Sub demo()
    test (Range("A1:A4"))
    test Range("A1:A4")
End Sub

Sub test(vRange As Variant)
    MsgBox "Type passed is: " & TypeName(vRange)
End Sub

The first call to test gives "Variant()"
The second call gives "Range"

[werafa]

Thanks Jan,

I did notice the use of that word in your post, but don't yet really understand the difference.
my current understanding is that the first example would appear in 'test' as an array of values. is this correct?

if so, I can see how a range could be evaluated, wut a workbook or worksheet could not

Thanks
Werafa

[Jan Karel Pieterse]

My example is practically the same as this:

    MsgBox TypeName((Range("A1:A4")))

versus:

    MsgBox TypeName(Range("A1:A4"))

So if you try this:

    MsgBox TypeName((ActiveWorkbook))

you indeed get the same 438 error as in your code when there are parentheses that should be omitted.

[werafa]

It is,
and I believe I begin to see.

Thank you