formula into VBA

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
Office Version
  1. 2021
Platform
  1. Windows
How would I write the following formula for use in VBA?


=IF (H1>=G1+2,"yes","no")


Thank you

Vinnie
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Code:
    ActiveCell.FormulaR1C1 = "=IF(RC[7]>=RC[6]+2,""yes"",""no"")"
Hope that helps,

Smitty
 
Upvote 0
Vinnie

What exactly do you mean?

Do you want to place that formula in a cell?
Code:
ActiveCell.Formula= "=IF(H1>=G1+2," & Chr(34) & "yes" & Chr(34) & "," & Chr(34) & "no" & Chr(34) & ")"
 
Upvote 0
Well, you don't want to concatenate 7 times with variant functions for a double-quote... :confused:

Like Smitty said, use the Macro recorder for syntax.
 
Upvote 0
NateO said:
Well, don't concatenate 7 times with variant functions for a double-quote... :confused:

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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
NateO said:
And the hit for Concatenation?

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. :)
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.
 
Upvote 0
Norie said:
This type of operation will probably been done once, then the formula copied.
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. :)
Yes in this case.
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.
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.
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.
 
Upvote 0

Forum statistics

Threads
1,216,108
Messages
6,128,872
Members
449,475
Latest member
Parik11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top