VBA Concatenate

[sb1222]

Hi,
I'm having problems with concatenating string variables together.

This code works:
Dim text As String
Dim front As String
Dim middle As String
Dim back As String
Dim formula As Variant

text = Mid(ActiveCell.formula, 2)
front = "=IF(ISERROR(" & text
middle = "),'N/A'," & text
back = ")"

But, this piece does NOT work:

formula = front& middle & back

I get an compiling error expecting an end of a statement.

Any thoughts?

Thanks

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Frank Kabel]

Hi
your code snippet works for me. In which line do you get the compile
error?

 

[Chip Pearson]

Make sure that there is a space before each '&' character.
Otherwise, the compiler will try to use the '&' character as a
type conversion (to Long) operator, and you'll get a compiler
error on the variable that follows.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dana DeLouis]

A little off topic. Just personal preference would do it something like
this.

Sub Demo()
Dim MyFormula As String
Const Fx As String = "=IF(ISERROR(#),""'N/A'"",#)"

MyFormula = Replace(Fx, "#", Mid$(ActiveCell.formula, 2))
End Sub

Or use "WorksheetFunction.Substitute(..." instead of Replace.

Also, in the formula above, notice how Excel is insisting that the "f" in
"ActiveCell.formula" is a small letter. Your code also is using a small "f"
in ".formula" It should be ".Formula".
I am not sure if it is correct to say that it "Should be", but a "Key" word
that is not Capitalized may indicate a problem somewhere.

This is because I copied your code that had this part...

This forces all words with "Formula" to use "formula." When I delete your
line of code, the small "f" still stays. This is a known problem with
Excel.

To correct this "feature" of Excel, go to the top of your module and type:
Dim Formula

Then delete that line. Your code should now read correctly as
"...Mid$(ActiveCell.Formula, 2)..."

HTH. :>)