Back to Excel Newsletter Archives
FUTURE OF EXCEL |EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS FUTURE OF EXCEL
Microsoft will be releasing the new version of Office in late 2006. So far the new Excel version is shaping up to be one of the most exciting updates since Excel 97. Despite rumors that Excel will be moving away from VBA, it looks like VBA will be the language of Excel for a few years yet. Most changes seem to be in area of the removal of some long standing limitations.
3 of the most anticipated changes are as shown below. Personally, I'm not keen to see an increase in total rows available, at least not until Excel becomes a true relational database (multiple tables) rather than a flat line database (single table). The increase in Column numbers is a welcome change, although 16000 seems like over-kill.
The total number of available columns in Excel
Old Limit: 256 (2^8)
New Limit: 16k (2^14)
The total number of available rows in Excel
Old Limit: 64k (2^16)
New Limit: 1M (2^20)
Total amount of PC memory that Excel can use
Old Limit: 1GB
New Limit: Maximum allowed by Windows
To read more about the limitation and get a link to the official Microsoft Blog, go to our forum here .
This month I thought I would show you all how we can have font color of specified cells change color based on up to 6 conditions.
Follow this link if you are familiar with Conditional Formatting :
Follow this link if you are not familiar with Excel Custom Formats :
In this example we will have 6 conditions for font color as shown below.
If value less or equal to 0, then make font Red.
If value greater than 0 but less than or equal to 20, then make font Green.
If value greater than 20 but less than 31 , then make font Blue.
If value is between 31 and 40 make font Tan.
If value is between 41 and 50 make font Grey-50%.
If value is greater than, or equal to 51 make font Brown.
To accomplish this we must select the cells to be formatted and then go to Format>Format Cells - Number and use the Custom Format as shown below;
Click Ok to return to Excel. Now with the cells still selected go to Format>Conditional Formatting and set the format criteria as shown below;
1) Condition 1: Cell Value: Between: 31 and 40. Click Format and choose Tan for the Font color.
2) Condition 2: Cell Value: Between: 41 and 50. Click Format and choose Grey-50% for the Font color.
3) Condition 3: Cell Value: Greater than or equal to: 51. Click Format and choose Brown for the Font color.
Now with combination of Custom Formats and Conditional Formatting we have 6 conditions for the font color.
RETURN THE LAST DAY OF ANY GIVEN MONTH
Go here first if you are not familiar with how Excel stores dates and times
One common request is to use Excel to work out the last day of any given month, or date. For example, let's suppose A1 houses the date 23-Jun-2005 and you wish to have Excel reference this cell and return the date of the last day of the month Jun. The formula below will do this;
Where, we are adding 1 month to the date in A1 (June becomes July) and using zero for the day which forces Excel to return the last day of the month before, i.e. July. Or, you can hard code the date as shown below.
There is a slightly shorter method but it can only be used when/if the Analysis Toolpak is installed. That is, Tools>Add-ins and check Analysis Toolpak. Then you you can use the EOMONTH function as shown below.
STOP CASE SENSITIVITY IN VBA
By default, Excel VBA code is case sensitive and uses Binary comparisons. This means that "Cat" and "cat" are not seen as being the same. There are many times however, where you may like Excel VBA to not use Binary comparisons and have "Cat" = "cat". This can be done in at least 2 ways.
We can use the Ucase function to ensure all text we compare will be in upper case. Take the example macro below which will show a message box if it encounters any cell in A1:A10 of the active sheet that contains any case variation of the word "CAT".
Sub CompareText()Dim rCell As Range For Each rCell In Range("A1:A10") If UCase(rCell) = "CAT" Then MsgBox rCell.Address & " has " & rCell & " in it" End If Next rCellEnd Sub
Option Compare Text
The other method we can use will make ALL procedures in a specified Module non-case sensitive. We do this by placing the words Option Compare Text at the very top of the Module we wish to make non-case sensitive. For example, any Procedures placed within the same Module as the Procedure below will no longer be case sensitive. To make ALL procedures within the Module case sensitive again we would replace Option Compare Text with Option Compare Binary.
Option Compare Text----------------------------------------------------------------------------Sub OptionCompareText()Dim rCell As Range For Each rCell In Range("A1:A10") If rCell = "cat" Then MsgBox rCell.Address & " has " & rCell & " in it" End If Next rCellEnd Sub
SEND EMAIL FROM EXCEL
Excel VBA allows us to send emails from within Excel and even send the Workbook as an attachment.
The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients. As the code below specifies the Active Workbook the code is best stored in your Personal.xls
Sub SendActiveWorkbook() ActiveWorkbook.SendMail _ Recipients:="email@example.com", _ Subject:="Try Me " & Format(Date, "dd/mmm/yy")End Sub
If you only wish to send one Worksheet from a Workbook we can use the method shown below. It creates a new Workbook housing ONLY the sheet we copy. It then sends the 1 sheet Workbook as an attachment, then closes the new Workbook without saving.
Sub Send1Sheet_ActiveWorkbook()'Create a new Workbook Containing 1 Sheet _ and sends as attachment. With ActiveWorkbook .Sheets(1).Copy .SendMail Recipients:="firstname.lastname@example.org", _ Subject:="Try Me " & Format(Date, "dd/mmm/yy") .Close SaveChanges:=False End WithEnd Sub
Another method we can use is the Route Method, it routes the workbook, using the workbook's current routing slip. This allows us to specify numerous recipients and have the Workbook send to the next in the routing slip. When sent, the text below is automatically added to the body of the email;
"The enclosed document has a routing slip. When you are done reviewing this document, choose Send To from the Microsoft Office Excel File menu. Then select Next Routing Recipient to continue the routing."
In the case of the code below, the Workbook would first be sent to email@example.com who would take the needed action and then go File>Send to>Next Routing Recipient and the Workbook would then be automatically sent to the next Recipient. In this case, firstname.lastname@example.org
Sub RouteActiveWorkbook() With ActiveWorkbook .HasRoutingSlip = True With .RoutingSlip .Delivery = xlOneAfterAnother .Recipients = Array("email@example.com", _ "firstname.lastname@example.org", "email@example.com") .Subject = "Check This Out" .Message = "Please fill in the Workbook and send it on." End With .Route End WithEnd SubUntil next month, keep Excelling!
Software Categories Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.