formula into VBA

The easiest way to do it is start the recorder, then in the cell that houses the formula-->F2-->Enter to capture the formula-->stop recording and check the VBE.

You'll get something like this: Hope that helps,

Smitty

Vinnie

What exactly do you mean?

Do you want to place that formula in a cell?

Well, you don't want to concatenate 7 times with variant functions for a double-quote...

Like Smitty said, use the Macro recorder for syntax.

NateO said:

Well, don't concatenate 7 times with variant functions for a double-quote...

Click to expand...

Why not?

In my experience using Chr(34) is clearer than doubling up the quotes.

It's easier to debug and easier to get the correct syntax.

Well, then your experience is different then mine, then.

First, Functions, as do strings, require processing, you'd need a timer to to be sure which gets burned into and evaluated faster in memory.

But here's what you do not need a timer for:

1) Concatenating over and over again, in VB[A], should be considered carefully, as should ReDim Preserve, these can result in very expensive operations. See the following:

http://groups.google.com/group/microsoft.public.vb.com/msg/a4738fcf6faa185a

I try to think twice when I concatenate...

2) What's the return of Chr(34)? A string, yes? If you want a string, don't use a Variant function, use a String function, it's more efficient and it returns what you want. See the following:

http://www.aivosto.com/vbtips/stringopt.html

And note, you should probably use ChrW$(), if anything, but don't.

3) Variant functions/concatenation is not easier to trouble-shoot than a string; they are harder to maintain. How could that be clearer to the human eye?

Combine that with being inefficient, it just doesn't make sense, especially in larger processes...

Nate

It makes perfect sense to me.

Have you never sat looking at code that uses the doubling up quotes method wondering.

1 Why it won't compile

2 Why it doesn't generate the expected formula

3 Why you keep on getting run time errors

Most of the time I'm not particularly concerned with the speed of the code and I'm pretty sure there are plenty of other coding practices that impact on that more than something like this.

Okay,

Let's pretend that double-quotes are extremely confusing for argument's sake (which I'm not buying, incidentally)....

Why would you use an ANSI, Variant function to return a Unicode String?

And the hit for Concatenation?

You don't really need to worry about the lower-level implications that I'm talking about; use the Macro Recorder for this, it will do the work for you. And in this case, it gets it right.

NateO said:

And the hit for Concatenation?

Click to expand...

Nate

This type of operation will probably been done once, then the formula copied.

NateO said:

use the Macro Recorder for this, it will do the work for you. And in this case, it gets it right.

Click to expand...

Yes in this case.

But what happens when the code needs altered, where you are only using the recorder as a start.

By the way both of the links you posted refer to VB not VBA, but I suppose the same concepts apply.

Norie said:

This type of operation will probably been done once, then the formula copied.

Click to expand...

Who's to say that? And you're [needlessly] concatenating 7 times in one string.

Norie said:

Nathan said:

use the Macro Recorder for this, it will do the work for you. And in this case, it gets it right.

Click to expand...

Yes in this case.

Click to expand...

That's really all I'm trying to speak to here...

Norie said:

But what happens when the code needs altered, where you are only using the recorder as a start.

Click to expand...

Obviously the answer will dramatically vary with the goal set, so there's not much point in my trying to answer this, here. I suspect though, that I won't start concatenating strings where possible...

Norie said:

By the way both of the links you posted refer to VB not VBA, but I suppose the same concepts apply.

Click to expand...

True. VBA handles strings, ANSI and Unicode, the same way VB does, and Chr(), ChrW(), Chr$() and ChrW$() all function the same as they do in VB.

The following is still relevant in VBA:

http://www.romanpress.com/Articles/Strings_R/Strings.htm

And here's an example of working with bytes of a Unicode String in VB[A]:

http://www.mrexcel.com/board2/viewtopic.php?p=859919#859919

And yes, your Macro recorder isn't going to kick that out... I'm not saying it solves all of the World's problems, here.

You can modify perfectly good syntax that slows down the operation and convolutes the code if you like, but in good faith, I'm going to advise the OP not to do that.