Run-time 1004 - "=" within a string

[newtolivieri]

Hi all,

First post here.

I'm getting '1004' on this instruction: Selection.Formula = ConcFormula

ConcFormula is a String which has: =CONCATENATE("(select ";$B$6;" from ";$B$2;" where ";$B$3;" = ";"'";$B$4;"' AND ";$C$3;" = ";"'";$C$4;"'";")")

The desired result is an SQL Select statement.

At first, I thought the error was with the double quotes with the string while composing the contents, so I used a bunch of "chr(34)" in the code, but it turns out the problem, at least now, is with the '=' character within the string, for when I remove it, it doesn't give me '1004'.

Thanks,
Newton

 

[newtolivieri]

Forgot to mention, I tried removing the '=' character from the ConcFormula string and then doing:

Selection.Formula = Chr(61) & ConcFormula

...but it didn't work.

Thanks again!

 

[Norie]

Newton

Why do you think it's the "="?

It's more likely to be the ;.

In VBA the seperator is , not ;.

What's the actual formula you want to put on the worksheet?

 

[newtolivieri]

Hi Norie, first of all, thanks for the quick reply.

The ";" separator is due my system regional settings... it is the content that goes within the content of the formula... in the code, ";" is string content and not VBA code...

I think it's the "=" because when I remove it from the string, I don't get the error.

In other words, when I use:

SelectFormula = "CONCATENATE(" & Chr(34) & "(select " & Chr(34) & ";"

...instead of:

SelectFormula = "=CONCATENATE(" & Chr(34) & "(select " & Chr(34) & ";"

...the final result on the cell is:

CONCATENATE("(select ";$B$9;" from ";$B$2;" where ";$B$3;" = ";"'";$B$4;"' AND ";$C$3;" = ";"'";$C$4;"'";")")

...adding "=" to the beginning of this content gets me the SQL Select statement I want... in other words, cell content like this:

=CONCATENATE("(select ";$B$9;" from ";$B$2;" where ";$B$3;" = ";"'";$B$4;"' AND ";$C$3;" = ";"'";$C$4;"'";")")

...results in:

(select employee_id from employee where first_name = 'John' AND last_name = 'Smith')

 

[Norie]

I know that ; is your seperator, but VBA doesn't recognise local seperators - try using , instead of ;.

If you want to put a formula in a cell you must have the "=", so that definitely isn't the problem.

One method you could try to use would be to use & instead of CONCATENATE.

& is the string concatenation and if you use it then you shouldn't need to worry about seperators.

I started rewriting the formula but had to leave it, I'll try and take a look later.

Creating this type of formula in code can be tricky especially when you need to mix ' and " etc.

 

[Richard Schollar]

As Norie suggested just try writing it like this:

ConcFormula = "=""(select "" & $B$6 & "" from "" & $B$2 & "" where "" & $B$3 & "" = '"" & $B$4 & ""' AND "" & $C$3 & "" = '"" & $C$4 & ""';"")"

and try assigning this to your cell.

 

[newtolivieri]

Hi all, once again, thanks for the quick reply.

The suggestion of not using CONCATENATE works just fine, and, was my previous solution. The problem I had with it was that the resulting SQL statement was "static". If the value of the cells I referenced changed, which they did quite often, it was necessary to go through the whole sheet in search of cells that referenced the changed cells and run the code again.

Now, Norie's suggestion of replacing the ';' for ',' worked as fine as it gets. I did a simple "Replace All" and the code worked just as expected. Fantastic!

Lesson learned! Thanks once again for the help!

 

[Norie]

Well I'm glad you've got it sorted but I honestly don't see why using & instead of CONCATENATE would make the cell references static.

Aren't they static anyway since they are all absolute references?

One thing I was going to ask, and it's perhaps irrelavant now, do you actually need the formula to construct the SQL statements?

You could create them using code, which might be easier.

It would certainly avoid all the doubling up of quotes and other faffing about.

 

[mikerickson]

Is ConcFormula dimensioned as Variant or String? Try changing it to the other. Occasionaly, I've gotten an error when a variable is assigned to a .Formula property is dimensioned the wrong way. (Unfortuantly, I don't remember whether Variant or String is the one that always works.)

 

[newtolivieri]

Hi mikerickson, my problem was solved by Norie. VBA apparently does not operate on different regional settings, which means I was using the wrong separator. Thanks for replying! =)

Hi Norie, since now I don't have the urgency of solving the problem anymore, I guess I can write a longer explanation of how I came to the solution that lead to my problem, and you can read it when you have the time. =)

A teacher of mine in college once told me: "In software development, by solving a problem, you usually create another."

What I witnessed in this particular situation was a series of small problems... =)

My first problem: I needed an SQL population and cleanup script for tables within a database. Since I haven't had to work with SQL professionally before, I got someone to help me out... he requested the information in an Excel file in a format difficult to maintain (one table per sheet, with around 20 tables) with a script generation procedure that required a tool, which ties me up in terms of flexibility (flexibility is what I like about Excel, by the way)... so I figured a way to generate the scripts I needed with all the tables within the same sheet... at this point, I didn't need values that depended on other tables... so, my first version worked...

My second problem: as development progressed, we found out we need INSERT statements that would use values from other table, which, at the time of script generation were unknown to us for they were automatically generated... a solution to this was to use a SELECT statement within the INSERT to fetch the unknown values based on known values... so far so good...

My third problem: the code to solve the second problem worked for a short time, because a few days later, we came across a table in which a single known value wasn't enough to fetch the automatically generated value for the known values weren't unique... so we needed a SELECT statement that would combine multiple knowns values in the WHERE clause... so here I came up with the "static" version in which I didn't use CONCATENATE...

My forth problem: both the load and cleanup script were working 100%... a couple of days before testing in the customer's environment, we realized some values within the script needed to be changed... so we did... the results were a considerable number of inconsistencies that came up because our SELECT statements were referring to cells whose values had changed... so, for every change, we had to dig through the sheet looking for inconsistencies caused by the changes... this is where I came up with the CONCATENATE solution: using the CONCATENATE formula referencing cells, the resulting SELECT statements would always "look at" the current value of the cell... the previous version "looked at" the cell value during code execution and wouldn't reflect further changes unless the code was executed again for each of the SELECT statement cells, and these cells had to be found among many...

My fifth problem was the one you helped me to solve... =)

Do things make sense now? I have basically two macros in this file... one generates the SQL statement and saves it to the cell I want... the other generates the INSERT statements and DELETE statements in reversed order (so I don't have foreign key constraints to deal with) and creates two *.sql files...

As I mentioned, I never had to work with SQL script files before, and this is the way I thought simple enough to implement and maintain... =)

Sorry for the long post here... and thanks again for the help... I can say I spent around a whole day on it... =)