Software Search, Categories and Specials Contact us:  [email protected]

  click here for our Excel Newsletter Archives
 

Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download

 

AnalyserXL - Download

 

DownloaderXL - Download

 

Smart VBA - Download

 

TraderXL - Download

OZGRID EXCEL NEWSLETTER

 

DOWNLOADS FOR XL - EXCEL TRAINING - EXCEL TEMPLATES - EXCEL DASHBOARD REPORTS

CODE VBA - FREE DEMO DOWNLOADS - MORE SOFTWARE

CLICK HERE TO VISIT OUR FREE EXCEL 24/7 HELP FORUM

Note - This Newsletter will be published mid-month, every month or so on the Website and via the FORUM.  If you would like to subscribe to it, please email: [email protected] and we will put you on the mailing list and send it directly to your email.  We will never send anything other than the Newsletter to you. 

Welcome to the second edition of the OzGrid Newsletter. It has been a busy couple of months here at OzGrid, many changes are in the pipeline to make the Site more user-friendly and we are working on some new ideas.  

I am pleased to announce that the winners of the January Code Golf Competition are:
  • Code Golf VBA - 2 Joint Winners - SMC and Luke M
  • Code Golf Excel - NBVC

SEE THE WINNING ANSWERS HERE.

I hope you enjoy the second edition........

OZGRID CODE GOLF

In every Edtion of the Newsletter we will run a Code Golf competition. This Newsletters Code Golf Questions have been set by Luke M (VBA Question)and NBVC (Formula Question). Any member of the Forum is entitled to answer the questions. If you would like to become a Member, you can JOIN HERE

Here are the Rules:

Questions:

1.  The question must clearly state whether a VBA or FORMULA answer is required.

2.  The question must include a target compatibility configuration. (e.g. "Must work on Windows 10/Excel 2013")

3.  There must be at least one known valid answer to the question.

4.  The question must supply all the required information for an answer (e.g. Example data set supplied)

5.  The question must comply with the forum rules. (e.g. No questions about password breaking)

6.  The question cannot give permission to amend any of the other rules for this game.

7.  Do not cross post the question looking for an answer on other forums

Answers:

1.  Answers to be posted on the OZGRID CODE GOLF Sub-Forum.

2.  Members can only post 1 answer per question, any further answers will be deleted and will not count.

3.  Once posted, answers will be "soft deleted" to prevent other from seeing.

4.  VBA answers must supply all the code required to fully execute the answer without error. (no undeclared UDFs!)

5.  VBA answers must only contain one executable statement per line, use of colons (":") is not allowed.

6.  Formula answers must be worksheet formulas only and cannot make use of any VBA or UDFs

7.  The member that sets the question is not allowed to answer.

8.  All answers must comply with the forum rules. (e.g. Use [CODE][/CODE] tags for all VBA answers.)

General:

QUESTIONS:

QUESTION 1 (VBA) - Set by Luke M

Create a user-defined function to test primality of a number. A number N is defined as prime if it's only divisible by N and 1. E.g., 1 is not prime (only divisible by one number), 2 is prime, 4 is not prime. Results can be verified using this site: http://www.numberempire.com/primenumbers.php. The function should work in a spreadsheet, be compatible with XL2010 on Win7, and must use the following declaration:

QUESTION 2 (EXCEL)- Set by NBVC

Find the shortest formula possible to extract the middle initial from a Name. There may not be a middle initial, so return blank. There may be a period, so remove it. There may be a full middle name, extract only first character.

Following is an example of expected output:

 

POST YOUR ANSWERS TO THE CODE GOLF QUESTIONS HERE

____________________________________________________________________________________________________________________________________________________________________________

AUTOFILTERING COMBOBOX

 DOWNLOAD EXAMPLE

This is tool for the user to choose from a list. Its a combo box on a user form, but with two twists.

1) As the user types in the box, the list is filtered.

2) It is cast as a public function of the user form. The coding to call it.

VB:

    Dim myVal As String

    MyVal = Userform1.ChooseFromList(Array("a", "b", "c", "d"), "Pick one")

The xlFilterStyle argument of the ChooseFromList function controls what kind of filtering xlBeginsWith, xlContains, xlEndsWith, xlDoesNotContain or xlNone.

And it allows for a default answer.

It also accepts ranges as the source of the list.

The four buttons show some ways of how it might be used. If you "Choose a group", try to choose a group that contains Brendan.

 

EXCEL'S LOGICAL FUNCTIONS

This Edition I thought we would look at some of Excel's Logical Functions.  These consist of

1.     AND

2.     FALSE

3.     IF

4.     NOT

5.     OR

6.     TRUE

Without a doubt, the most commonly used Logical function would be the IF Function. The IF Function, like all the logical functions, will return TRUE or FALSE. Let's look at a simple use of the IF function.  In this example we will use it to determine whether the cell A1 is greater than 100.  If it is, then we will multiply that number by 10, otherwise if it's not greater than 100 we will divide 10 by it.

IF(A1>100,A1*10,10/A1)

As you can see, the Syntax for the IF function is:

=if(logical_test,value_if_true,value_if_false)

Note the Argument value_if_false is not bolded. This is because we do not have to supply the False argument. If we omit it, our formula would return the BooleanFALSE if it evaluates to False.

Let us now suppose we need to check if cell A1 is between the value 100 and 200. For this we need to Nest the AND function into our logical_test for the IF function.

=IF(AND(A1>100,A1<200),A1*10,10/A1)

The AND has a Syntax of:

=AND(logical1,logical2,...u

It will return True ONLY if all supplied logical arguments evaluate to True. If only one from many, evaluate to False and the rest are True, the AND Function will return False. As you can see we have told the AND function to check whether cell A1 is between 100 and 200 by telling it to evaluate the 2 logical arguments we have supplied. The result (TRUE or FALSE) is returned to the logical_test argument of the IF Function.

Let's now suppose we want to check the contents of 2 other cells and if either one is greater than 100 multiply A1 by 10.

=IF(OR(A2>100,A3>100),A1*10,10/A1)

The OR has a Syntax of

=OR(logical1,logical2,...up to 30 logical

Very much the same as the AND function. The one and only difference is, only one of all supplied logical arguments needs to be true for the OR Function to return TRUE.  In our example this means that if either A2 or A3 is greater than 100, the result TRUE is returned to the logical_test argument of the IF Function.

So far we have been making one VERY big assumption and that is, Cell A1 will house a number. Try typing some text in cell A1 and all our Function will return the #VALUE! error.  This is simply because we cannot multiply or divide with text. To account for these types of situations we need to either first check A1 houses a number.

=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,10/A1),"")

The above is one way to do this and if cell A1 does not house a number then return "" (empty text). There is still a potential problem when A1 is equal to zero.  We end up with the #DIV/0! error.  Meaning we are trying to divide by zero and that's not possible.  To account for this also, we could use

=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(A1>0,10/A1,"")),"")

This will now prevent trying to divide 10 by zero. If we wanted we could take, what I call the blanket approach, by using one of Excel's Information Functions.  In this case the ISERROR or ISERR Functions. These have a Syntax of.

=ISERROR(value)

and

=ISERR(value)

They are both very similar but while the ISERROR checks for all errors (and returns TRUE or FALSE), the ISERR checks for all errors except the #N/A! and again returns True or False. Meaning we could use:

=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ISERR(10/A1),"",A1/10)),"")

This will prevent any errors in regards to trying to divide by zero. The problem with this approach is you could well be masking an error that you perhaps should be aware of! One way to overcome this potential problem is to use the ERROR.TYPE Function.  It has the Syntax

=ERROR.TYPE(error_val)

and again, will evaluate to either True or False. The error_val's are:

#NULL! = 1
#DIV/0! = 2
#VALUE! = 3
#REF! = 4
#NAME? = 5
#NUM! = 6
#N/A = 7
Anything else#N/A

Meaning, we could use:

=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ERROR.TYPE(10/A1)=2,"",A1/10)),"")

This will only prevent the #DIV/0! error should A1 be zero.

Error checking/trapping in Worksheet functions can get quite complex very quickly. Even with our above formula we could still encounter an error within the OR Function. If this happens our OR function will return neither TRUE or FALSE, it will return an error and that will force our formula to again return an error. So as you can see, a simple formula can soon turn into a monster if we are to try and account for all possible errors. Truth is, it is not feasible to always to try and trap all errors. The best way to prevent errors is to prevent them at the source. In this case, the source is cells A1, A2 and A3 and the easiest way to ensure no invalid data goes into to these cells is by using the Validation feature found under Data on the main menu.

 

If you have any suggestions for improvements to this Newsletter, please email me - [email protected].  Thank you.

SEE THE WORK THAT THE DAVE HAWLEY FOUNDATION IS DOING HERE

 

EXCEL TRAINING - EXCEL HELP FORUM - EXCEL FORMULAS - EXCEL VBA MACROS - VBA RESOURCES - MORE FREE STUFF

 
See Also: Excel Templates Index Or, All Software

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft