VBA variable string concatenation

Question

I have a variable a = "C2" and variable b = "C3"

I need to do something like

ws.Range("A2").Value = "=" & a &"" ""& b

which should return:

=C2&" "&C3

in an excel formula however I cant seem to get this to work anyone have a solution??

Are you trying to get your cell to have the formula =C2&" "&C3, or do you want it to show the values of C2 and C3, separated by a space? Try ws.Range("A2").Formula = "=" & a & "&"" ""&" & b. If you want the values of your variables, separated by a space, use ws.Range("A2").Value = a & " " & b. — BruceWayne
– BruceWayne, Commented Jan 13, 2016 at 21:38
@BruceWayne this is the correct answer the first one worked great. Thanks! — Tyler Cowan
– Tyler Cowan, Commented Jan 13, 2016 at 22:05

BruceWayne · Accepted Answer · 2016-01-13 22:11:08Z

2

From my comment,

ws.Range("A2").Formula = "=" & a & "&"" ""&" & b

This works because you need to use .Formula instead of .Value.

answered Jan 13, 2016 at 22:11

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyler Cowan Over a year ago

Actually also works with .Value interestingly enough

BruceWayne Over a year ago

@TylerCowan - UUhmmm, I'm just going to ignore that then, to keep my sanity :P I thought I was getting the hang of VBA, and then you tell me I can do .Value just the same. If someone can comment as to why, that'd be appreciated! Perhaps it's because you're using an = at the start, so of course, when Excel sees an = at the start, it uses it as a formula. I wonder then, what the "real" big difference in using .Value and .Formula for setting formulas is.

Tyler Cowan Over a year ago

Imagine you have integers, doubles for certain calculations and if you use .formula you gonna get screwed. Because .FORMULA always return a String. (Any value set to .formula without is a plain value not a formula) Unless you have exception handling and the extras ready in your code. Whereas .VALUE returns your data's data type as you expect them to be. In terms of retrieving formula: Value is one way road in case to set formula but not to retrieve. stackoverflow.com/questions/13687800/…

Collectives™ on Stack Overflow

VBA variable string concatenation

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related