What does & do in VBA

Question

I am new to VBA, I got a question on &, What does the two & do in this code below, can we delete the last one?

Range("H2").Formula = "=COUNTIF(H4:H" & WKB2.Range("F" &     
Rows.Count).End(xlUp).Row & ",""AMB"")"

Some other code has only one & but still working. for example:

ActiveSheet.Range("F5:F" & lastrow1 - 1).Formula = "=IF(C5>0,""CC"",""AMB"")"

what is the difference here.

Thanks in advance.

score 2 · Answer 1 · answered Jul 03 '18 at 04:31

2

& is one of the two possible operators for string concatenations in VBA.
(+ is also possible, but IMO & is better)

E.g. "A" & "B" will result in "AB".

Or, to use one of your examples - let's say lastrow1 = 5, so lastrow1 - 1 = 4, so this:

ActiveSheet.Range("F5:F" & lastrow1 - 1)

...will become:

ActiveSheet.Range("F5:F4")

answered Jul 03 '18 at 04:31

Christian Specht

35,843
15
128
182

Thanks Christian, it is clearly explained what does it do, would you mind to insight on the first code. will the first code still working by deleting the second &? As per below: Range("H2").Formula = "=COUNTIF(H4:H" & WKB2.Range("F" & Rows.Count).End(xlUp).Row ",""AMB"")" – Classique Jul 03 '18 at 05:08
@Classique What do you mean? Just delete `&`? – JohnyL Jul 03 '18 at 05:23
@JohnyL Yep, just delete the second & from the first code, will it still working, and why. – Classique Jul 03 '18 at 05:32
@Classique Of course, it won't work - there will be error. Didn't you try it? :) – JohnyL Jul 03 '18 at 05:58
Deleting will **not** work, because the second `&` is needed to concatenate `",""AMB"")"` with the rest of the string. I don't know Excel formulas, though, so I'm not sure what `COUNTIF` does and what parameters it needs. – Christian Specht Jul 03 '18 at 05:59
@johnyL, I tried and it didn't work, but the second code i posted is very similar, which doesn't request the second & to work. so i am confused. – Classique Jul 03 '18 at 06:17
@Classique You mean `ActiveSheet.Range("F5:F" & lastrow1 - 1)`? And how deleting `&` doesn't cause an error? – JohnyL Jul 03 '18 at 06:19
@ChristianSpecht, Thanks so much， I think I got it, correct me if i am wrong, you mean variables in excel formula need to be like &variables& to concatenate with the rest of formula right? – Classique Jul 03 '18 at 06:28
@JohnyL,I think I have now got it, in VBA code you can concatenate variables by one &, but in excel formula, variable needs to both start and end with &. – Classique Jul 03 '18 at 06:31
In Excel formula `&` serves as concatenation - you can use it instead of `CONCATENATE` function. For instance, put this into cell: `=1 & 2 & 3`. You'll get `123` string. It will be formatted as string, however it will recognized by Excel as number. If you want it to be a real number, you need following: `=--(1 & 2 & 3)`. – JohnyL Jul 03 '18 at 08:15

score 0 · Accepted Answer · answered Jul 03 '18 at 06:37

For Excel formula and VBA - Exactly where and whether 1 or 2 '&'s depends on your use case.

To concatenate with rest of formula you need &variable& (so 2 &s).

If there is nothing to concatenate at the end, but there is something at the beginning then use &variable.

If there is nothing to concatenate at the beginning but there is something to concatenate at end then use variable&.

If there is nothing to concatenate at beginning or at end, then just use variable.

Hope this makes sense.

What does & do in VBA

2 Answers2