1

Problem

I am working with some Excel slicers through VBA, and need to filter (a varying number of) items at the same time.

The slicer commands include the """ and "&" characters, however, which obviously is problematic.

I've tried treating the """ and "&" characters individually, e.g. "Some text" & """ & str1 & "&" & "more text", as well as trying to bundle it all together, e.g. "Some text"" & str1 & "&more text"

Neither approach seems to work, generating a "Compile error: Expected: End of statement".

Question

Is there a better way to include the "&" and """ characters in a concatenation?

More Context

This is the actual string layout I am trying to end up with, after all concatenation is complete: 

"[Jobs].[ParentJob].&[Code1]", "[Jobs].[ParentJob].&[Code2]", "[Jobs].[ParentJob].&[Code3]"

Where Jobs and ParentJob remain the same (they are slicer categories and should be part of the string); but Code1, Code2 etc... are variables which are to be concatenated one by one as a loop runs.

I'm confident running the loop and concatenating the right parts in the right places, I just can't find any resources anywhere that explain how to include " and & in concatenations when they are in a more complex format.

sdunnim
  • 181
  • 13
  • 1
    To add the ampersand to a string you can use something like CHR(38): `"Some text" & """ & str1 & CHR(38) & "more` – Xabier Aug 20 '19 at 09:03

2 Answers2

3

For a single " I normally use """" (4 doble quotes).

It's annoying but it works pretty good. So for your test, if I do:

Debug.Print """" & "[Jobs].[ParentJob].&[Code1]" & """" & ", " & """" & _ "[Jobs].[ParentJob].&[Code2]" & """" & ", " & """" & "[Jobs].[ParentJob].&[Code3]" & """"

in the inmediate Window I get as output the text:

"[Jobs].[ParentJob].&[Code1]", "[Jobs].[ParentJob].&[Code2]", "[Jobs].[ParentJob].&[Code3]"

Hope this helps

UPDATE: For the char & there is no problem as long as it's between double quotes. Handle it as any other char text. So doing "&" will return & and doing "[Jobs].[ParentJob].&[Code1]", will return [Jobs].[ParentJob].&[Code1]

UPDATE 2: Another option to make the code more readable would be assigning the """" to a string variable. Something like this:

Dim DblQuote As String

DblQuote = """"

Debug.Print DblQuote & "[Jobs].[ParentJob].&[Code1]" & DblQuote & ", " & DblQuote & _
"[Jobs].[ParentJob].&[Code2]" & DblQuote & ", " & DblQuote & "[Jobs].[ParentJob].&[Code3]" & DblQuote

This will output the text you need, but it's easier to work with it and make the code readable.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • That's worked perfectly thanks - do you by any chance know why this works and/or why it doesn't seem to be documented anywhere? Or is it just one of those things? Seems kind of counterintuitive to my average at best coding experience... – sdunnim Aug 20 '19 at 09:13
  • 1
    To be honest, I did not find any official documentation. I saw this long time ago in a forum/website and because it worked, I use it. There are more ways to do it, but I'm afraid I can't give a documented answer. The only thing I found is https://learn.microsoft.com/en-us/office/vba/access/concepts/criteria-expressions/include-quotation-marks-in-string-expressions but in my daily routine I work as suggested in the answer. – Foxfire And Burns And Burns Aug 20 '19 at 09:38
1

You have the option to use multiple quotes as suggested by @foxfire. But I would also recommend using the CHR function in VBA.

In VBA you can use Chr(38) & Chr(34) for "&" and (double quote - ") respectively. i.e:

Chr(38) = &
and
Chr(34) = "

Gangula
  • 5,193
  • 4
  • 30
  • 59