VBA String without a space doesnt work, with a space does work

Question

I am playing around with VBA in Excel, and cant for the life of me figure out why this error is popping up.

The following code works:

Cells(1, 1) = " =if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "

The following code doesnt work:

Cells(1, 1) = "=if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "

So Just by removing the space to make the line of code insert a formula rather than a string causes the compiler to through up error 1004.

In other lines ive used itll insert a formula no problem, but concatenating text with a local variable seems to throw this up. Any ideas?

It is because the formula is not complete. You are missing all your ) and so excel cannot put it in as it is a bad formula. — Scott Craner
– Scott Craner, Commented Jan 6, 2017 at 14:20
I dont really care if the formula works at the moment (im just messing around at the moment) but the error isnt within excel, its within the code, ie the compiler wont allow the string to be inserted, even though its wrong. It should be an excel error, not a VBA error — Giovanni
– Giovanni, Commented Jan 6, 2017 at 14:21
I should clarify; the formula was complete when I started it all, and then started removing things to see if it was those characters throwing up the error, until I found it was the = sign touching the " without a space. — Giovanni
– Giovanni, Commented Jan 6, 2017 at 14:22
That is correct the compiler will not allow a formula that is incorrect to be entered. If you tried to enter this directly into excel an error would occur vba is throwing that error. — Scott Craner
– Scott Craner, Commented Jan 6, 2017 at 14:23
Cells(1, 1) = "=if(counta('" & aLoc & "Subbies'!$A$2:$A$20)>0, ""YES"", ""NO"" )" — Scott Craner
– Scott Craner, Commented Jan 6, 2017 at 14:24

Scott Craner · Accepted Answer · 2017-01-06 14:53:17Z

4

Excel will not allow an incorrect formula to be entered.

When putting a formula directly in the sheet you get the an error if it is not a correct formula and the cell remains in edit mode till either deleted or fixed.

The 1004 error is just vba representing that error.

To help find the error in the formula, place your string in a variable then debug.print it to see what is not correct:

t = "=if(counta('" & aLoc & "Subbies'!$A$2:$A$20)>0, ""YES"", ""NO"" )"
Debug.Print t

This will put the formula in the immediate window as it would in excel, allowing the developer to check to see if there is an error.

Bottom line:

You can put a string of what ever you want in a cell, but if you are trying to put a formula it must be complete and accurate.

answered Jan 6, 2017 at 14:53

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

Comments

Gary's Student · Accepted Answer · 2017-01-06 14:35:56Z

0

Excel is complaining that you are trying to put a bad formula in a cell. If you want to put a "formula as text" in a cell, then:

Sub DontQuoteMe()
    Cells(1, 1) = "'=if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "
End Sub

(using the space accomplishes the same thing)

answered Jan 6, 2017 at 14:35

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA String without a space doesnt work, with a space does work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related