VBA Access Arguments Passing Values

Question

Access 2013

I'm calling a formula to modify a string and it's changing the values w/in the parent sub.

Example:

Debug.Print Str 'Hello World my name is bob
BOBexists = InStringChceck(Str,"bob")
Debug.Print Str 'HELLO WORLD MY NAME IS BOB
Debug.Print BOBexists 'TRUE

I've used this function, InStringCheck, in Excel VBA before (and it's just an example, all of my string tools are doing this same thing now and I don't know why)

Function InStringCheck(Phrase as string, Term as string) as Boolean
    Phrase = UCase(Phrase)
    Term = UCase(Term)
    if instr(1, Phrase, Term) then InStringCheck = True else InStringCheck = False
end function

In several of my functions I manipulate the input variables, to arrive at a solution, but I don't want those manipulations to persist outside of the function unless I pass them back up - some how they're being passed up, but they're not dimed as public variables

use the ByVal keyword in the parameters declarations InStringCheck(ByVal Phrase as string, ByVal Term as string) — Sorceri
– Sorceri, Commented Jun 30, 2016 at 15:37
As an aside, you can change if instr(1, Phrase, Term) then InStringCheck = True else InStringCheck = False to InStringCheck = InStr(1, Phrase, Term). Never return True or False based on a Boolean condition. It's already True or False by definition. Just return the Boolean condition itself. You don't have to interrogate it and return another Boolean. — ManoDestra
– ManoDestra, Commented Jun 30, 2016 at 16:16
Other side note: Unless you specify vbBinaryCompare in the call or in Option Compare, InStr() is case-insensitive. So UCase isn't needed. — Andre
– Andre, Commented Jun 30, 2016 at 16:47

Mathieu Guindon · Accepted Answer · 2016-06-30 16:03:37Z

VBA parameters are implicitly passed by reference (ByRef). This means you're passing a reference to the value, not the value itself: mutating that value inside the procedure will result in that mutated value being visible to the calling code.

This is often used as a trick to return multiple values from a function/procedure:

Public Sub DoSomething(ByVal inValue1 As Integer, ByRef outResult1 As Integer, ...)

You have two options:

Pass the parameters by value (ByVal)
Introduce local variables and mutate them instead of mutating the paramters (and heck, pass the parameters ByRef explicitly)

If you have lots of occurrences of parameters being implicitly passed ByRef in your project, fixing them everywhere can easily get tedious. With Rubberduck you can easily locate all occurrences, navigate there, and apply appropriate fixes:

Disclaimer: I'm heavily involved in the Rubberduck project.

user3598756 · Accepted Answer · 2016-06-30 16:11:49Z

2

Building a little on @Sorcer's answer, VBA has default Sub/Functions parameters passing "by reference" (i. e.: "ByRef" keyword assumed if not specified) so that if you don't want their "inside" modifications survive outside them you have to explicitly type "ByVal" keyword before them in the arguments list.

But you have the option to avoid such modifications take place altoghether by using StrComp():

Function InStringCheck(Phrase as string, Term as string) as Boolean
    InStringCheck = StrComp(Phrase, Term, vbTextCompare) = 0
End Function

Which could also lead you to avoid the use of InStringCheck() in favour of a direct use of StrComp() in your code

answered Jun 30, 2016 at 16:11

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA Access Arguments Passing Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related