vba - Which would yield better performance in general? -

i have small dilemma. know, defining variables types , avoiding usage of variants obvious performance trick. problem i'm trying write library of routines work implicitly-typed arguments (basically variants).

take example:

sub test(a string) ' implicit byref     debug.print end sub 

nothing crazy, right? if did test "abc", works expected. problem arises when try pass value array (test array("abc")(0)) or return value routine while chaining. i'd compile error saying "byref argument type mismatch".

i need these routines take in various types of arguments , typecast them when possible. thought of following:

sub test(byval string) ' explicit byval     debug.print end sub 

now works fine. ultimately, question is: performance gain achieved explicitly defining argument types worth tradeoff of performance loss imposed making copies of argument values use of byval? know there cases 1 better other, general usage library, method more suitable?

---

i have setup small benchmark. i'm not using powerful computer in world (core i3-2120, 32-bit windows 7, 4 gb ram), can't these apply other setups.

private declare ptrsafe function gettickcount lib "kernel32" () long  private sub noop(paramarray self()) ' adds small overhead; not sure if there's better built-in routine use end sub  private sub byreference(byref argument variant) ' byref , variant default keywords if keywords ignored     noop argument end sub  private sub byvalue(byval argument string)     noop argument end sub  private sub benchmark()     dim index long, argument variant: argument = "abc"     = gettickcount / 1000     index = 1 10000000         byreference argument     next     b = gettickcount / 1000     index = 1 10000000         byvalue argument     next     debug.print b - a, (gettickcount / 1000) - b ' seconds; higher precision if divide them before taking differences end sub 

result: 3.88499999999476 4.99199999999837

we can see obvious performance loss string (a short one, anyway).

now, if change argument 12345 , routine definition's argument type long, get:

result: 4.07200000000012 4.05599999999686

i don't know whether or not that's within margin of error, tell different types behave differently.

feel free try different types on own system , let me know.

i made quick test, have below. calling byref faster, comes no surprise, amount of time small don't have care. tested on win7 3 years old laptop, nothing fancy.

running 1 million iterations, result 141 ms byref , 281 ms byval. changing string used parameter every other time increased time, both methods, assume due string handling.

my conclusion: choose calling method needs, not speed.

option explicit  private mlngstart long private declare function gettickcount lib "kernel32" () long  sub test()      testperformance 1000000, false     testperformance 1000000, true  end sub  sub testperformance(iterations, changestring)     dim s string     s = "hello world, long string test happen when pass value"     starttimer     dim long     = 1 iterations         if changestring s = iif(i mod 2 = 0, "hello world", "hello world, long string test happen when pass value")         call f1(s)     next     debug.print iterations, changestring, "byref: " & endtimer      starttimer     = 1 iterations         if changestring s = iif(i mod 2 = 0, "hello world", "hello world, long string test happen when pass value")         call f2(s)     next     debug.print iterations, changestring, "byval: " & endtimer  end sub  sub f1(byref s string)     '     if s = "x"         debug.print s     end if end sub  sub f2(byval s string)     if s = "x"         debug.print s     end if end sub  public sub starttimer()     mlngstart = gettickcount end sub  public function endtimer() long     endtimer = (gettickcount - mlngstart) end function 

output:

 1000000      false         byref: 141  1000000      false         byval: 281  1000000      true          byref: 655  1000000      true          byval: 764