1

Is there any difference between using a builtin function returning a string such as Left or using the same function with a $ appended (Left$)?

The output of this:

Debug.Print Left("Foo", 2)
Debug.Print Left$("Foo", 2)

is always

Fo
Fo

I suspect that it's strictly the same thing and that the $ versions exist only for some compatibility reasons.

Jabberwocky
  • 48,281
  • 17
  • 65
  • 115
  • 2
    Well this might be useful https://stackoverflow.com/a/19365070/5811078 – zipa Oct 03 '18 at 13:40
  • 1
    With `x = Left(a, b)` both `x` and `a` are variants compared to `Left$()` where both are `string`. – Alex K. Oct 03 '18 at 13:42
  • 3
    The $ variety (e.g. Left$, Mid$, etc) are measurably faster that their non-$ equivalents. Worth pursuing if you are doing hundreds of thousands of iterations or calculations. –  Oct 03 '18 at 13:47
  • 1
    @ScottCraner My bad - I was looking at the accepted answer (which doesn't answer the question). – Comintern Oct 03 '18 at 14:04
  • @ScottCraner the linked duplicate has a highly-voted answer that is all about *type hints*, which have absolutely nothing to do with `Left`/`Left$` function aliases. – Mathieu Guindon Oct 03 '18 at 14:32
  • @MathieuGuindon but the other answers all say the same thing as the accepted answer below. And anyone that disagrees can certainly vote to reopen. – Scott Craner Oct 03 '18 at 16:04

2 Answers2

5

The typed functions (those ending with a $) return a String. The un-typed versions return a Variant. Internally, these are handled by a pair of different functions (in the case of Left, it is _B_str_Left and _B_var_Left).

If you are assigning the return value to a String or a parameter expecting a String, using the typed version (Left$) avoids an implicit cast to a Variant. Similarly, if you're assigning to a Variant, using the un-typed version avoids a cast.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Worth noting that while the string function is *usually* preferable, `Left$` will blow up with a type mismatch given a `Null`, whereas `Left` will happily take it (well, and then blow up with another error because obviously you can't get the N left-most characters of a `Null` value, but consider `UCase`/`UCase$` then) - `Null` would only be relevant in a very limited number of scenarios outside of MS-Access. – Mathieu Guindon Oct 03 '18 at 14:31
3

Left$() wants a string as argument but Left() expects a variant. Therefore using Left$() is faster if you know you will always pass it a string.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • But this works too: `x = Left$(123, 2)` (result = `12`). That means that `123` is implicitely converted to string. Can you confirm? – Jabberwocky Oct 03 '18 at 13:52
  • 2
    @Jabberwocky VBA does type coercion / implicit type conversions liberally. `123` is definitely implicitly converted to a `String`, *it has to be*, for the signature of `Left$` takes a `String` parameter. – Mathieu Guindon Oct 03 '18 at 14:28