CODEWITHSUNDEEP
stringms-accessvba
Schalton
Schalton

Reputation: 3104

VBA Access Arguments Passing Values

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

Upvotes: 2

Views: 3608

Answers (2)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

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:

Rubberduck inspection results

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

Upvotes: 5

Related Questions