Save As .xlsm Only

With the advent of Excel 2007 Microsoft introduced new file extensions for Excel.  Instead of having only .xls for all workbooks we now have .xlsb for Excel Binary Workbooks, .xlsm for Excel Macro-Enabled Workbooks, .xlsx for Excel Workbooks and a multitude of other formats from Templates to Text.  One of the potential issues of these different formats when using Macros is the loss of the Macros when the workbook is not saved as a Macro-Enabled Workbook.  Even though the User receives a message that the Macros will be removed the message is often ignored resulting in a copy of the workbook minus the original Macros.

One solution to this issue is to prevent users from saving your workbook with any file extension other than .xlsm.  The code below will prevent this.

 
This code has to be in the Workbook module rather than a regular module.  To access the Workbook module click the Developer tab and then click the Visual Basic icon to display display  the Visual Basic editor.

Right click on ThisWorkbook and select View Code

Enter the code in the the Worksheet Module, close the editor and save the workbook.

If you have questions regarding this let me know and I'd be happy to help if I can.

Date Time Stamp

Received a request from my son last week for a way to enter a date/time stamp in a cell when any value is entered in another cell.  Also, if the entered value is removed, remove the time stamp.  He's using Excel to track specific information regarding projects assigned to him.  When he enters a Project name in Column A he wants the date/time stamp in Column C of the same row.  When the Resolution of the Project is entered in Column E a date/time stamp is placed in Column F of the same row.

This was accomplished by using the Worksheet_Change event.  You can do a Google search on "Worksheet_Change" more detailed information about this event but suffice to say that it allows you to do something when a cell is changed on a worksheet.  The only way I've previously used this event was to run a macro but in this case we're simply wanting to insert a value into a specific cell.  

Here's the basic layout of the worksheet that needs the date/time stamp in columns C and F.

The following code is written to the Private Module of the Worksheet where it will be used rather than a general Module in the workbook.  The easiest way to do this is to right click on the worksheet tab and select View Code.  Paste this code into the Private Module.

There may be (and probably is) a better way to accomplish this but this how I did it.  If you know of a better or more efficient way to accomplish this I would appreciate hearing from you.

WORKDAY Function

The WORKDAY function is similar to the NETWORKDAYS function.  It differs in that it calculates a date X number of days from the Start_date.  The "target" date can be either before or after the Start_date.  This function is frequently used the Start or End date for a project.

The syntax for the WORKDAY function is: =WORKDAY(Start_day, Days, Holidays)

Where:
"Start_date" = The start date of the period to be calculated
"Days" = The number of days for the period
"Holidays" = The date or range of dates to be excluded from the Period.  The "Holidays" argument is optional.


In the post for NETWORKDAYS I said that "The data could be entered into a table similar to the one here."  Actually, only the list of Holidays is typically entered in a table like this and is usually on a hidden sheet.  The "Start_date" and "Days" are entered as arguments in the formula wherever the function is being used in the workbook.


As with NETWORKDAYS the Holiday argument is optional and is not limited to holidays but can be used for any day to be excluded from the calculation.


If the function is to be used to calculate the beginning date rather than the ending date the "Days" argument is entered as a negative.

NETWORKDAYS Function

Requests for various workbooks have come in over the years that included the need to determine the number of billing or invoicing days in the period selected by the user.  If the company invoices every workday then this can be done using the NETWORKDAYS function.  This function counts the number of days between two dates inclusive and excludes weekends.  Holidays and/or any specified day off can also be excluded.

The syntax for the NETWORKDAYS function is:
=NETWORKDAYS(Start_date, End_date,Holidays)

Where:
"Start_date" = The first day of the selected Period
"End_date" = The last day of the selected Period
"Holidays" = The date or range of dates to be excluded from the Period.  The "Holidays" argument is optional.


The data could be entered into a table similar to the one here.  This would usually be done on a hidden worksheet



If =NETWORKDAYS(B1, B2, B4:B11) is entered using the data above the result will be 22.  This result is then available for other calculations in the workbook.

NOTE:  If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Excel's Convert Function

I frequent John Walkenbach's website The Spreadsheet Page (spreadsheetpage.com) and always learn something new.  Today I downloaded a workbook that lists all the functions in Excel 2007.  There are 340 functions in the list, most of which I've never used and CONVERT is one that I'd never used or even heard of.

The CONVERT function "Converts a number from one measurement system to another" and is listed in the Engineering Category of Functions.  Using this function you can easily convert miles to kilometers,  pounds to kilograms, degrees Celsius to degrees Fahrenheit and many others.

I've already found a practical use for this function.  Using it I found that a 1.5 Liter bottle of Bourbon contains 33.8 1.5 oz shots.

You can learn more about this function at
http://office.microsoft.com/client/helppreview.aspx?AssetID=HP100623111033&QueryID=03RAPgEGJ&respos=1&rt=2&ns=EXCEL&lcid=1033&pid=CH100645391033

Here's a link to the Microsoft Excel 2007 Help list of 2007 functions
http://office.microsoft.com/client/helppreview.aspx?AssetId=HA102775249990&lcid=1033&NS=EXCEL&Version=12&pid=CH010064502&CTT=4#BM1

Useful Shortcut Keys

Here are some shortcut keys that I frequently use.

Ctl+ Arrow keys
Select a cell in a range of data
The Ctl key + the down Arrow key will take you from the active cell to the last populated cell in the column
The Ctl key + the up Arrow key will take you from the active cell to the first populated cell in the column
The Ctl key + the right Arrow key will take you from the active cell to the last populated cell in the row
The Ctl key + the left Arrow key will take you from the active cell to the first populated cell in the row

Ctl + Shift + the Arrow keys as above will select all cells from active cell to the last populated cell.  The selection of a range of cells allows you to copy, cut, format etc.

Ctl + C copies the active cell

Ctl + V pastes whatever has been copied to the active cell

Ctl + Z  undo last action

Ctl + A select all cell in current region

Ctl + F find

Selection using ALL, Division, Region, Branch

I've tried several methods of allowing users to make selections of ALL, Region and Branch and came up with a  way of doing it but only if the Branches assigned to a Region do no overlap.  This has it's obvious limitations and will definitely not work for Customer with a large number of Branches.  I was discussing this in one of the Excel classes I was conducting at our User Group meeting in Las Vegas and was asked why I didn't just utilize the available fields in the Branch Item Master file to make this selection.  I had thought of this solution previously but could not determine how it could be used.  This was because I was limiting myself to using a range of values in my SQL statement.  Once I began to consider other ways of accomplishing the goal it seems that the solution came very quickly.  Rather than using a range of values it was only necessary to use the OR statement.  The statement works like this..... Company = value OR Division = value OR Region = value OR Branch = value.  When the parameters values are passed to the MS Query  only one is valid, the others are invalid.  Such a simple solution and so easy to implement.