Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 25th, 2011, 07:42 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default use of OR in a formula (not as in and/or type meaning)

I am faced with an extremely complex workbook with formulas that contain code I have not used or seen before. If anyone can explain what OR means in the following, I would be very grateful. I do not see how the OR could mean literally, or, and it does not refer to a worksheet column of OR:

'=INT(0.5+RC[1]/12)+IF(OR(Input!R[16]C="A", Input!R[16]C="SA", Input!R[16]C="Q", Input!R[16]C="BM"),0.001,0)+IF(OR(Input!R[16]C[2]="C",Input!R[16]C[2]="BC",Input!R[16]C[2]="RC"),0.002,0)+IF(OR(Input!R[16]C[2]="B",Input!R[16]C[2]="BC",Input!R[16]C[2]="BRET"),0.004,0)+IF(OR(Input!R[16]C[2]="R",Input!R[16]C[2]="RC",Input!R[16]C[2]="RRET"),0.008,0)

The above is all one line in the Excel cell, but for whatever reason did a couple of line breaks when I pasted it above. I added the leading apostrophe in the first line. There are innumerable cells within many tabs of this workbook with these designations in them.
 
Old April 25th, 2011, 08:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there..

OR means or in the way you mean it...

OR in excel can be used like a fuction, but will return true or false depending on how every part is evaluated.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 25th, 2011, 08:51 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kind of like a shorter way to get the result of an IF statement?
 
Old April 25th, 2011, 09:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

It's easier for non programer people to understand this that or/and expression and how are they evaluated...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 26th, 2011, 07:49 AM
Registered User
 
Join Date: Apr 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, the "OR" in the statement you have shown is used to determine what value to use in the calculation in the cell in which the statement is written. That is to say on sheet INPUT a cell can have the values "A", "SA", "Q" and so on and depending on which is entered on the cell on sheet INPUT the different values are used in the calculation where this statement is.

Does that help?

Best regards

RM
 
Old April 26th, 2011, 05:00 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

The Or() function takes two or more conditions and if any of those conditions is true, the Or() function returns true. I usually use it for the condition part of an If() function.
Simpler looking examples might help.

Or(1<2,1=2) will evaluate to TRUE because the first test 1 < 2, is true.

Or(3<2,1=2) will evaluate to FALSE because neither test is true: 3 is not less than 2, and 1 does not equal 2.

A simple If() function looks like this:

If(1<2,"This is true","This is false")

But say, for example you have a gym and members can be either standard members or gold members. Either way, they are a member. In this case you could use the Or() function for the test part of the If() function like so:

If(Or(A1="Standard",A1="Gold"),"This person is a member","This person is not a current member")

Hope that clarifies it a bit for you

P.S. That workbook has the scent of a behemoth in dire need of being rewritten using VBA. If you need some more help, post here, I'm pretty good with worksheet formulas. Good luck.

Last edited by mtranchi; April 26th, 2011 at 05:14 PM..
 
Old April 26th, 2011, 11:03 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you to all, especially mtranchi with the examples that drove home how and why someone might use this. To me, in a way, but only in a way, the examples last given seem similar to a nested if statement, but pushing it a little closer to a CASE statement.

I truly never knew that OR function existed.

Regarding the comment that the entire workbook needs a lot of work, that is an significant understatement.

The entire project has somewhere around 15 - 20 tabs with a total of 17 macros. The person who originally did it disabled the auto-update, or whatever it is called, where a change in a base cell automatically uptdates cells with formulas referring to that cell. He said it interfered with his macros and the user needed to use F9 to update the entire workbook.

The workbook is a financial planning application. So there are certain input cells on the INPUT tab that cause lots of calculations in formulas on the other tabs.

The guy that wrote all the code also has a group of code that tells the program how aspects of how a graph (a pie chart) should look. The first thing I did was rem out his code, format the pie chart the way we want it, and let formulas update the base data with NO code determining the format of the pie chart. I have long believed that any time there is a reasonable solution that does not involve code, we should avoid the code.

I love what we can do with code, but it can be so easy to foul when external circumstances change and so difficult to go back and adjust to account for external changes. I have yet to see any really well documented code, including my own.

Anyway, again, thank you all for the help. I am sure I will run into some more issues with this over the next week or so.





Similar Threads
Thread Thread Starter Forum Replies Last Post
meaning post and get amged Pro PHP 1 August 1st, 2007 06:33 AM
Meaning Of MultiLanguage nir_pankaj C# 8 March 8th, 2007 01:10 PM
meaning of a warning mastrgamr C++ Programming 7 December 22nd, 2006 08:16 AM
Meaning mani_he Beginning PHP 3 January 19th, 2005 12:26 AM
"p2p" what's meaning? shuicanyi Forum and Wrox.com Feedback 4 April 16th, 2004 12:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.