Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Wednesday, September 30, 2009 – Permalink –

When 28 is 30

How long is a month?


When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer.

If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned.
You expect a value of 30 days to be returned for every full month. (12*30=360)

This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function.

To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax: 

=DAYS360(cell number of start date,cell number of end date,TRUE)

Using FALSE or omitting the third parameter uses the U.S. method

Support.microsoft.com
An unexpected value with the DAYS360 function




See all Topics

Labels: , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment

  Thursday, September 24, 2009 – Permalink –

Shut it All Down

Close the spreadsheet and Excel


As it comes out of the box, at this point, Excel 2007 is set to display spreadsheets on the task bar. No big deal 2003 did too.

If, however, you only have one workbook open and want the application to shut down when you close the book you must make a little change.

Just as before, you must deselect Windows in the Taskbar from the View Options.

In 2007 it is located by clicking on the logo icon, then choose Excel Options. The Windows entry is on the Personalize screen






See all Topics

Labels: ,


<Doug Klippert@ 3:44 AM

Comments: Post a Comment

  Thursday, September 17, 2009 – Permalink –

Lock the Barn

Protect your work



John Walkenbach has put together an FAQ on Workbook/Worksheet/VBA protection.
Spreadsheet Protection FAQ

The Microsoft Knowledge Base article KB 293445 Has a list of references to protection information.


Microsoft Excel provides multiple layers of protection to allow you to control who can access and change your data:
  • Worksheet protection: You can protect elements on a worksheet (for example, cells with formulas) from all user access, or you can grant individual users access to the ranges that you specify.
  • Workbook-level protection: You can apply protection to workbook elements, and you can protect a workbook file from being viewed and edited. If a workbook is shared, you can protect it from being returned to exclusive use and prevent the change history from being deleted.
... articles address some of the more frequently asked questions about workbook and worksheet protection in Excel:
  • How can I grant only a few users access to a range in my worksheet?
  • Why are users not allowed to edit the ranges that I established permissions for?
  • What new features are available in workbook protection?
  • Why don't the permissions that I set on ranges in my worksheets carry over to Windows 98 computers?
Here is more information
Overview of security and protection in Excel




See all Topics

Labels: , ,


<Doug Klippert@ 3:44 AM

Comments:
You might want to check out Mike Alexander's blog post about how easy it is to remove worksheet protection in Excel 2007.

http://datapigtechnologies.com/blog/index.php/hack-into-a-protected-excel-2007-sheet/
# posted by Blogger JP : 8:10 AM
 
Post a Comment

  Saturday, September 12, 2009 – Permalink –

Declaring Multiple Variables

Declare each one


When setting up a macro in VBA, if you want to declare multiple variables in one line of code, be sure to specify the type for each variable, even if the variables are the same type. Avoid code like the following:

Dim strFName, strLName, strMI As String

In such a case, only the last variable, strMI, is actually declared as a String type. The first two variables are designated by default as Variant data types.

To correctly declare the three variables, you would use the statement:

Dim strFName As String, strLName As String, strMI As String




See all Topics

Labels:


<Doug Klippert@ 6:47 AM

Comments: Post a Comment

  Friday, September 11, 2009 – Permalink –

AutoShapes

Drawing bar objects



Kim Hedrich has put together a series of basic articles on AutoShapes for TechTrax.

AutoShapesPart 1 - How to draw circles, ovals, squares and rectangles; also modifying fill and line colour

AutoShapes Part 2 - Fill Effects

AutoShapes Part 3 - Shadows and 3-D

AutoShapes - Text Inside a Shape




See all Topics

Labels: ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment

  Thursday, September 10, 2009 – Permalink –

Trouble with Save?

Also great notification service



You may experience issues when you try to save a Microsoft Excel file if one or more of the following conditions are true:
  • You save an Excel file to a network drive where you have restricted permissions.
  • You save an Excel file to a location that does not have sufficient drive space.
  • The connection to the Excel file has been lost.
  • There is a conflict with an antivirus software program.
  • You save an Excel file that is shared.
  • The 218-character path limitation has been exceeded when you save an Excel file.
  • The Transition Formula Evaluation feature is turned on in Excel.
  • The file was created from a template that contains embedded objects.

How to troubleshoot errors when you save Excel files. KB 271513.
Receive Free Email Alerts every time Microsoft Publishes NEW Support or Knowledge Base Articles! kbAlertz.com is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies, you subscribe to.
kbAlertz See all Topics

Labels:


<Doug Klippert@ 3:50 AM

Comments: Post a Comment

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically


Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.


Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs


Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment