VBA Declaring arguments outside a function

Question

I have vba code which opens a word document based on a template and when finished, runs the code below:

Public Sub Destroy(doc As Word.Document, app As Word.Application)

    If Not (doc Is Nothing) Then doc.Close SaveChanges:=wdDoNotSaveChanges
    If app.Documents.Count = 0 Then app.Quit wdDoNotSaveChanges
    Set app = Nothing

End Sub

(this means that the app only closes if there are no other documents open and doesnt leave a blank application loaded when finished either)

I would like to extend this to excel and possibly other applications in the future; but rather than write a separate function for every different application, I wondered if I could have one function do-it-all.

The problem I'm finding is the declaration of arguments "doc As Word.Document" and "app As Word.Application".... is there a way of declaring what "doc" and "app" are in the calling program, and then getting the type definition of them inside my function to decide what to do depending on what type of application I choose to destroy()?

Edit:

I'm happy with the code, but on running a quick test in the code below, I founf that byref and byval both didnt effect the value of myval:

Private Sub Command12_Click()

    Dim myval As Integer

    myval = 1

    MsgBox "the value of myval is " & myval
    doByVal (myval)
    MsgBox "the value of myval is " & myval
    doByRef (myval)
    MsgBox "the value of myval is " & myval

End Sub


Private Sub doByVal(ByVal a As Integer)
    a = a + 1
    MsgBox "byVal gives " & a
End Sub

Private Sub doByRef(ByRef a As Integer)
    a = a + 1
    MsgBox "byRef gives " & a
End Sub

score 3 · Accepted Answer · 2014-06-26T15:17:35.730

3

Sure. You may declare generic Objects as args of the function, and verify their actual type in function. Basically, your code frame will look like:

Public Sub Destroy(ByVal doc As Object, ByVal app As Object)

        If (TypeOf doc Is Word.Document) And (TypeOf app Is Word.Application) Then
                ' Word related stuff

        ElseIf (TypeOf doc Is Excel.Workbook) And (TypeOf app Is Excel.Application) Then
                ' Excel related stuff
        ' ...
        Else
                ' Do something about mixed cases, unhandled types etc.
        End If

End Sub

And here's passing args to Destroy function, as (a bit exaggerated) example:

Dim my_doc  As Excel.Workbooks
Dim my_app  As Excel.Application

Set my_app = Excel.Application
Set my_doc = my_app.Workbooks("IWantYouClosed.xlsx")

Call Destroy(my_doc, my_app)

edited Jun 26 '14 at 15:17

answered Jun 26 '14 at 14:32

surely NOT `ByVal`: Objects are *always* `ByRef` ! – iDevlop Jun 26 '14 at 14:37
Ah brilliant, that's exactly what I'm after, I read the other comment about late vs early binding and found I was already using late binding in my calling code. I assume this can work for any type as objects can be anything??? does this then take up more memory than writing several different functions (or is this trivial). Also, I'm not sure about the use of ByVal – Ian Jackson Jun 26 '14 at 14:39
@iDevlop I'm sorry to contradict, but to `Object`-type arguments `ByVal` and `ByRef` will ultimately do the same in this context. The difference is that using `ByVal` is slightly (i.e. almost imperceptibly) faster. That is because I pass `ByVal` a reference to the object (objects are never duplicated unless there's a method that clones them -- but this is not out of the box from the VBA language). – Jun 26 '14 at 14:43
@IanJackson If you have doubts about `ByVal`, please indulge me and try it once, to experiment. :-) I, for myself, am pretty sure that `ByVal` is okay in terms of `Object`-type arguments, but ultimately is you who needs to be convinced one way or the other about it. – Jun 26 '14 at 14:46
Passing `ByVal`a *reference* to an object ? That sound a bit like passing a `Ref`, isn't it ? Also, `ByVal` would mean you pass the document itself, which 1) is clearly not the case 2) would be very slow. Perhaps the compiler doesn't make an error here, but I am quite sure about my remark. Do you have any reference for what you say ? (I am always glad to learn) – iDevlop Jun 26 '14 at 14:49
3

If you pass an object ByVal, you pass a copy of the *pointer*. That means you can still manipulate the object just as if you passed it ByRef, but you can't make the original pointer refer to a different object. (look at the declaration for the `Worksheet_Change` event for example - Target (which is an Excel.Range object) is passed ByVal. – Rory Jun 26 '14 at 14:51
@CST-Link am I right in assuming that this would not destroy correctly if I passed word.document as this would copy the document, whereas its OK with objects as copying a reference is quicker than making a reference to a reference? – Ian Jackson Jun 26 '14 at 14:52
@iDevlop Here's the Official doc that I was referring to: http://msdn.microsoft.com/en-us/library/eek064h4.aspx Please consult the "Ability to Modify" table, where you'll see the possible side effects upon value-types (those that can be "converted" to `Variant`, usually assigned with `Let`), and reference-types (those can be converted to `Object`, usually assigned with `Set`) – Jun 26 '14 at 14:54
@IanJackson Yes, that was my point indeed. Also, look at the comment of Rory. – Jun 26 '14 at 14:55
@CST-Link I just tried this with ByVal and Word.Document and it still works... I'm not sure why – Ian Jackson Jun 26 '14 at 14:57
@IanJackson It works because (in our case) 1) doubly de-referencing a reference to a reference, and 2) de-referencing the value of a reference, in the end, will point to the same object. – Jun 26 '14 at 15:01
@CST-Link Indeed I found some traces of what you say, like http://stackoverflow.com/a/21841837/78522 I am still surprised, but OK, I learned something. So Thanks and +1 – iDevlop Jun 26 '14 at 15:04
@IanJackson Oh, I'm sorry, that was not my intention. I just don't know how else I could explain... how about this resource? http://www.cpearson.com/excel/byrefbyval.aspx – Jun 26 '14 at 15:16
@CST-Link, I tried making my own examples as seen in my edit above, but it didnt work as expected. It appears that byval and byref only really work when i use them in a function (not a sub) and i have to make sure that in the function, I set the function a return value – Ian Jackson Jun 26 '14 at 15:24
1

@IanJackson They work the same in any routine. When you pass parameters to a subroutine (or a function if you aren't storing the result), *do not enclose them in parentheses* unless a) you are using the Call statement; or b) you are trying to dereference/evaluate the parameter you are passing. Your example should read `doByRef myval` – Rory Jun 26 '14 at 15:29
1

@IanJackson The subtle points about `ByVal` and `ByRef` are far from exhausted by our discussion, and I'm not sure I know half of them. :-) First of all, our discussion before was about `Object`s or things that can be converted to `Object`s. `Variant`s and things that can be converted to `Variant`s (like the `Integer`s) have different rules. Also, the compiler can chose to ignore sometimes programmer's `ByRef` modifier. :-) Here's the big article on MSDN, if you have the patience to read it, with the sub-sections: http://msdn.microsoft.com/en-us/library/2ch70h3t.aspx – Jun 26 '14 at 15:33
PS where I said 'parameter' in that last comment, I meant 'argument'. It's been a long day. – Rory Jun 26 '14 at 15:39
Thank you for everyone's help. I learned much more than I thought I would. – Ian Jackson Jun 27 '14 at 07:51

score 0 · Answer 2 · edited Mar 20 '17 at 10:18

0

You are trying to use Early binding, and that requires creating a reference to the appropriate library (in Tools, References).
Alternatively, you can use Late binding, declaring doc As Object.
The web has lots of pages on "early binding vs late binding". One sample here.

edited Mar 20 '17 at 10:18

Community

1
1

answered Jun 26 '14 at 14:22

iDevlop

24,841
11
90
149

I have tried using doc as Object before and my code fell apart unless I explicitly stated it was a word.document and I don't know why – Ian Jackson Jun 26 '14 at 14:29

Ian Jackson · Answer 3 · 2014-06-27T09:26:53.300

Using the help given, this is the code I am now using, hope this helps someone.

Public Sub Document(ByVal doc As Object, ByVal app As Object)

    Select Case True

        Case (TypeOf doc Is Word.Document And TypeOf app Is Word.Application)
                If Not (doc Is Nothing) Then doc.Close SaveChanges:=wdDoNotSaveChanges
                If app.Documents.Count = 0 Then app.Quit wdDoNotSaveChanges
                Set app = Nothing

        Case (TypeOf doc Is Workbook And TypeOf app Is Excel.Application)
                'code for excel workbook

        Case Else
                MsgBox "Cannot recognise the document/application, or there may be a mismatch"

    End Select

End Sub

VBA Declaring arguments outside a function

3 Answers3