PDA

View Full Version : [SOLVED] Question: Passing objects to sub-procedures



werafa
10-15-2018, 04:43 PM
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
10-15-2018, 07:34 PM
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
10-15-2018, 08:06 PM
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
10-16-2018, 06:42 AM
Online help for 'Call'

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/call-statement?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3Fap pId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1008863)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue



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

https://stackoverflow.com/questions/2573597/should-i-use-call-keyword-in-vb-vba

Jan Karel Pieterse
10-16-2018, 07:11 AM
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
10-16-2018, 03:35 PM
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
10-17-2018, 01:18 AM
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
10-17-2018, 02:04 AM
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
10-17-2018, 02:28 AM
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
10-17-2018, 10:59 AM
It is,
and I believe I begin to see.

Thank you