Enquiry on Concatenate

Hi guys, may i ask how do i decode this?

ActiveCell.Value = "=sum(F2:" + LastCell + ")"

What does the 2 "+" signs beside the "LastCell" mean?

It's string concatenation. It means:

"=sum(F2:"

&

[value of]LastCell

&

")"

If LastCell = "E3", your returned string would be:

"=sum(F2:E3)"

Hi guys, thank you for your help.

Is there any general formula/code prompt for such combined sum and concatenate function?

This particular example of string concatentation is one we call "a bad idea", but you have to know the other ways to understand why most professionals hate it. I'll cover every way you can do it from "best" to "worst".

(A+) Use Interpolated Strings

This is a VB2017 feature. If you use $"" instead of just "" around a string, you're able to insert variables into the string by surrounding them with {}. For example:

Code:

---

Dim numberOfFruit = 10
Dim typeOfFruit = "orange"

MessageBox.Show($"There are {numberOfFruit} {typeOfFruit}s.")

---

So the equivalent of your code:

Code:

---

ActiveCell.Value = $"=sum(F2:{LastCell})"

---

(A+) Use String.Format()
If you're not in VB 2017 you can't use interpolated strings. But the String.Format() method's been around since the beginning. It's very similar to interpolated strings, only you have to use numbers and positional parameters. That's easier to show than explain:

Code:

---

Dim numberOfFruit = 10
Dim typeOfFruit = "orange"

MessageBox.Show(String.Format("There are {0} {1}s.", numberOfFruit, typeOfFruit)

---

It looks a lot like interpolated strings, just a little more clunky.

Code:

---

ActiveCell.Value = String.Format("=sum(F2:{0})", LastCell)

---

The reason these are A+ and the best is because they look the most like the string you want them to be. You don't have to mentally count quotes or ignore symbols. There's another approach using StringBuilder but let's ignore it for brevity, StringBuilder.AppendFormat() is basically the same as String.Format(). Let's talk about the "bad" approaches.

Also note that in both cases, I didn't have to call .ToString() on anything. .NET is smart enough to know if you're using these techniques, you want to convert stuff to String. There are some fancy ways to control HOW that happens, especially for numbers, but that's a large topic.

(C-) Using the & operator

You can glue multiple strings together with the & operator, which was specifically made for string concatenation.

Code:

---

Dim numberOfFruit = 10
Dim typeOfFruit = "orange"

MessageBox.Show("There are " & numberOfFruit.ToString() & " " & typeOfFruit & "s.")

---

Code:

---

ActiveCell.Value = "=sum(F2:" & LastCell & ")"

---

This was a pain in the butt to type, and I had to be careful to make sure I had spaces in all the right places. Notice how I even had to add some "empty" quotes to make sure there were spaces between variables. This is a nightmare, especially for larger strings, and especially when you have to incorporate quotes into them. You have to make sure to manually convert everything to a String, too. Generally I think most experts won't use it unless they're really gluing two different strings together.

(F-) Use the + operator
The "+" operator is in VB for "addition and string concatenation". It's an F- instead of C- because of that "addition and" part. VB has to decide which it's going to do, and sometimes it's not going to pick what you expect. If you happen to be jamming two numbers together, it will do actual addition on them instead of making a string. But more common, people expect it to be doing addition and it does concatentation:

Code:

---

MessageBox.Show(txtNumberOfApples.Text + txtNumberOfOranges.Text)

---

That won't show "30" if the text boxes contain "10" and "20". It will show "1020", because it did concatenation instead of addition. But it's also perfectly legal to write that line, since what the "+" does is different based on context. Welcome to the world of "easy" programming language features.

Aside from that, the "+" operator has all the same downsides as the "&" operator. It's harder to read long strings built with concatenation.

It's best to sort of forget that & and + can do concatenation, though some experts can point out there are situations where they might be faster. I tend to try to make my code "read well" first, then worry about performance if it feels slow.

One of the reasons why string concatenation is frowned upon comes into play in SQL ... people used to string concatenations will try to do that with their SQL Data too... and it'll be fine... up until Baba O'Reily registers. Or Bobby Drop Tables... http://xkcd.com/327/

I use & still, but it's become rare, opting for the more powerful string.format option.

-tg

@Sitten Spynne... I'd like to see some official documentation for your Interpolated Strings :D

BTW... They're a VB2015 addition to VB.

I'd love to see some official documentation, too. C# has it as a documented language feature. As far as I can tell, MS hasn't updated the VB language reference since VB 2013. I was surprised to find out the expanded tuple support is also available, too. I think MS believes VB developers don't care about new features so maybe they aren't investing in documenting them.