Returns a Variant (Date) containing the date of the last day of the week either before or after the EvalDate.
Syntax
LastDayOfWeek(EvalDate,LastDay, ReturnType)
EvalDate is the date to evaluated.
LastDay is a number between 1 and 7 representing the last day for the week If omitted the value is set to 1 (Sunday)
1 = Sunday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday
ReturnType is either 0 or 1. If omitted 0 is assumed. 0 returns the date of the last day of the week after the EvalDate, 1 returns the date of the last day of the week prior to the EvalDate.
Option Explicit
Public Function LastDayOfWeek(EvalDate As Date, Optional LastDay As Integer, Optional ReturnType As Integer) As Date
' Determines the date of the last day of the week based on the user's input
' EvalDate is the date to be evaluated
' LastDay is the weekday number of the last day of the week starting with
' 1 = Sunday, 2 = Monday, etc...
' ReturnType is either 0 (zero) or 1. Zero returns date of the last day of the week after the EvalDate
' 1 returns the date of the last day of the week prior to the EvalDate. If omitted zero is assumed.
Dim intEvalWeekday As Integer ' The weekday of the date to be evaluated
Dim intDifference As Integer
intEvalWeekday = Weekday(EvalDate, vbSunday)
' If the LastDay agument was omitted the value is set as 1 (Sunday).
' If the user enters a number that is not between 1 and 7 an error message
' is displayed.
If LastDay = 0 Then
LastDay = 1
ElseIf LastDay > 7 Or LastDay <>
MsgBox "Enter a number between 1 and 7 representing the day that is the last day of the week." _
& vbCr & vbCr & _
"1 = Sunday" & vbCr & "2 = Monday" & vbCr & "3 = Tuesday" & vbCr & _
"4 = Wednesday" & vbCr & "5 = Thursday" & vbCr & "6 = Friday" & vbCr & _
"7 = Saturday", vbCritical
LastDayOfWeek = "Error" ' This forces an error. Otherwise the formula will display "01/01/00"
Exit Function
End If
intDifference = LastDay - intEvalWeekday
' Determine output value of the function
If ReturnType = 0 Then ' Return the date for the last day of the week that follows the EvalDate
Select Case intDifference
Case 0
LastDayOfWeek = EvalDate
Case Is > 0
LastDayOfWeek = EvalDate + (LastDay - intEvalWeekday)
Case Is <>
LastDayOfWeek = EvalDate + (7 + LastDay - intEvalWeekday)
End Select
ElseIf ReturnType = 1 Then ' Return the date for the last day of the week that precedes the EvalDate
Select Case intDifference
Case 0
LastDayOfWeek = EvalDate
Case Is > 0
LastDayOfWeek = EvalDate - ((7 + intEvalWeekday) - LastDay)
Case Is <>
LastDayOfWeek = EvalDate + (LastDay - intEvalWeekday)
End Select
End If
End Function
Sunday, November 19, 2006
Shading Unlocked Cells
The below subs shade and unshade the unlocked cells in the active sheet. The third sub clears all the unlocked cells. When I create an application I protect the sheet and shade the cells that the users can input data into.
This is also an easy way to check what cells are unlocked.
Sub UnShade()
' Set the ColorIndex for all the unlocked cells on the
' ActiveSheet to 0.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).Interior.ColorIndex = 0
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
Sub Shade()
' Set the ColorIndex for all the unlocked cells on the
' ActiveSheet to 4.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).Interior.ColorIndex = 4
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
Sub ClearUnlockedCells()
' Clear contents of all unlocked cells in the active
' sheet.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).ClearContents
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
This is also an easy way to check what cells are unlocked.
Sub UnShade()
' Set the ColorIndex for all the unlocked cells on the
' ActiveSheet to 0.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).Interior.ColorIndex = 0
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
Sub Shade()
' Set the ColorIndex for all the unlocked cells on the
' ActiveSheet to 4.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).Interior.ColorIndex = 4
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
Sub ClearUnlockedCells()
' Clear contents of all unlocked cells in the active
' sheet.
ActiveSheet.Unprotect
Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)
llastColumn = rngLast.Column
lLastRow = rngLast.Row
Set rngUsed = Range("A1", rngLast)
r = 1
c = 1
Do Until r = lLastRow + 1
Do Until c = llastColumn + 1
If Cells(r, c).Locked = False Then
Cells(r, c).ClearContents
End If
c = c + 1
Loop
c = 1
r = r + 1
Loop
ActiveSheet.Protect
End Sub
Update All Pivot Tables in the Active Workbook
This sub updates all the pivot tables in the active workbook.
Sub UpdatePivotTables()
' Updates all the pivot tables in the ActiveWorkbook.
Application.ScreenUpdating = False
For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next
Application.ScreenUpdating = True
End Sub
Sub UpdatePivotTables()
' Updates all the pivot tables in the ActiveWorkbook.
Application.ScreenUpdating = False
For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next
Application.ScreenUpdating = True
End Sub
Saturday, October 28, 2006
Create Page Breaks in Excel
Often you might have a large table of data that needs to be grouped and printed on separate pages based on the value in a column. This procedure asks for the column containing the group by value, clears any previous page breaks, resets the page breaks and goes to print preview mode.
' Place page breaks on a worksheet based on the value in
' the column specified by the user. Sets the first row
' as headers to repeat on each page and activates the
' print preview mode.
Dim strColumn As String ' Column containing the break value
Dim lRows As Long ' Rows in the used range
Dim lTitleRow As Long ' Row containing the column headers
Dim vaValue1 As Variant ' 1st value to compare
Dim vaValue2 As Variant ' 2nd value to compare
Dim l As Long ' loop counter
lRows = ActiveSheet.UsedRange.Rows.Count
strColumn = InputBox("Enter the column containing the page break information.")
' Exit the sub if the user presses cancel.
If strColumn = "" Then
Exit Sub
End If
ActiveSheet.ResetAllPageBreaks
lTitleRow = 1
For l = 2 To lRows
Cells(l + 1, strColumn).Activate
vaValue1 = Cells(l, strColumn).Value
vaValue2 = Cells(l + 1, strColumn).Value
If vaValue2 <> vaValue1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End If
Next l
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
ActiveSheet.PrintPreview
Range("A1").Activate
End Sub
Sub PageBreaks()
' Place page breaks on a worksheet based on the value in
' the column specified by the user. Sets the first row
' as headers to repeat on each page and activates the
' print preview mode.
Dim strColumn As String ' Column containing the break value
Dim lRows As Long ' Rows in the used range
Dim lTitleRow As Long ' Row containing the column headers
Dim vaValue1 As Variant ' 1st value to compare
Dim vaValue2 As Variant ' 2nd value to compare
Dim l As Long ' loop counter
lRows = ActiveSheet.UsedRange.Rows.Count
strColumn = InputBox("Enter the column containing the page break information.")
' Exit the sub if the user presses cancel.
If strColumn = "" Then
Exit Sub
End If
ActiveSheet.ResetAllPageBreaks
lTitleRow = 1
For l = 2 To lRows
Cells(l + 1, strColumn).Activate
vaValue1 = Cells(l, strColumn).Value
vaValue2 = Cells(l + 1, strColumn).Value
If vaValue2 <> vaValue1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End If
Next l
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
ActiveSheet.PrintPreview
Range("A1").Activate
End Sub
Sunday, February 12, 2006
YubNub
YubNub is a web application that creates a "command line" for the internet. If you type a search query that does not start with a YubNub keyword you will perform a standard Google seach. On one level it is a kind of favorites menu (type "gma" and you will be taken to Gmail) and on the other it is a shortcut menu (type "gm" + an address and you will taken to the Google Maps results page for that address).
You are able to create your own commands and there are several that simply point to individual websites. Here are the most interesting commands I found during my first look, but there are many more (and many duplicates). The parameters are in square brackets.
Categories: Internet
You are able to create your own commands and there are several that simply point to individual websites. Here are the most interesting commands I found during my first look, but there are many more (and many duplicates). The parameters are in square brackets.
- g [search string] - Performs a Google search
- gi [search string] - Performs a Google Image search
- gm [search string] - Performs a Google Maps search
- wp [search string] - Performs a Wikipedia search
- bl - Launches Bloglines
- gma - Launches Gmail
- am [search string] - Perfoms an Amazon search
- y [search string] - Performs a Yahoo search
- d [search string] - Performs a Dictionary.com search
- gym [search string] - Performs a Google, Yahoo, and MSN search simultaneously and shows the results in separate frames.
Categories: Internet
Organizing Items In Outlook
Outlook has several ways to organize your items (emails, contacts, tasks, appointments, etc.).
This is the old way of doing things. In all likelihood you will file items and not be able to find them again. (just like in the traditional filing cabinets) I have only added one folder to the standard folders already in Outlook called "Tagged Mail". I will expalin why later.
Using Contacts
When you send or receive and email or respond to a meeting request that contains someone who is in your address book (contacts) that item is automatically associated with them. When you open a contact and select the Activity tab you will see all the items associated with that contact. If you create an appointment without using the meeting request you can associate the contact with the item on the bottom right of the screen used to create the appointment. If you want to associate a contact with an email message you can do so by right-clicking on the message and selecting options.
Using Categories
Outlook comes with a bunch of categories already set up. Unfortunately, the standard mail form does not give you the option to select categories while you are creating the message. You either have to do this in the options dialog box prior to sending or do it later. Other items like appointments and contacts allow you to categorize them while you are creating them. The key to utilizing categories is to use as many categories as needed to describe the item. Don't worry about using too many, just think of the things about the item that you will think of in the future to find it. There is no need to open the categories dialog box either. Just type the categories separated by a comma. If it is on the list it will be selected, if it is not it will be added.
How to Use Categories to Find Anything
The Tagged Mail folder I mentioned earlier is used to store all mail after it has been tagged (categorized). When an email comes in that I need to respond to I tag it before responding so that my response is pre-tagged. At the end of the day I go through my Sent Items and Inbox folders and tag all the emails. Mutiple emails can be tagged at the same time by selecting them and choosing categories from the right-click menu. All tagged messages are then put in the Tagged Mail folder.
The Tagged Mail folder is arranged by the categories so when you want to look for something scroll down to one of the appropriate categories and you will find it.
It's as simple as that. I created a custom message form that allows you to categorize messages as you create or read them, but changing the default mail message form is tricky. Once I figure that out though is will save a lot of clicking.
- Create folders to "file" the items. (Like you would physical files)
- Associate items with contacts.
- Associate items with categories.
This is the old way of doing things. In all likelihood you will file items and not be able to find them again. (just like in the traditional filing cabinets) I have only added one folder to the standard folders already in Outlook called "Tagged Mail". I will expalin why later.
Using Contacts
When you send or receive and email or respond to a meeting request that contains someone who is in your address book (contacts) that item is automatically associated with them. When you open a contact and select the Activity tab you will see all the items associated with that contact. If you create an appointment without using the meeting request you can associate the contact with the item on the bottom right of the screen used to create the appointment. If you want to associate a contact with an email message you can do so by right-clicking on the message and selecting options.
Using Categories
Outlook comes with a bunch of categories already set up. Unfortunately, the standard mail form does not give you the option to select categories while you are creating the message. You either have to do this in the options dialog box prior to sending or do it later. Other items like appointments and contacts allow you to categorize them while you are creating them. The key to utilizing categories is to use as many categories as needed to describe the item. Don't worry about using too many, just think of the things about the item that you will think of in the future to find it. There is no need to open the categories dialog box either. Just type the categories separated by a comma. If it is on the list it will be selected, if it is not it will be added.
How to Use Categories to Find Anything
The Tagged Mail folder I mentioned earlier is used to store all mail after it has been tagged (categorized). When an email comes in that I need to respond to I tag it before responding so that my response is pre-tagged. At the end of the day I go through my Sent Items and Inbox folders and tag all the emails. Mutiple emails can be tagged at the same time by selecting them and choosing categories from the right-click menu. All tagged messages are then put in the Tagged Mail folder.
The Tagged Mail folder is arranged by the categories so when you want to look for something scroll down to one of the appropriate categories and you will find it.
It's as simple as that. I created a custom message form that allows you to categorize messages as you create or read them, but changing the default mail message form is tricky. Once I figure that out though is will save a lot of clicking.
Sunday, February 05, 2006
Waterfalls
There are hundreds of waterfalls in the Portland area. During the winter it rains all the time and every vertical surface becomes a waterfall. This is a picture I took about a year ago.
Categories: Pictures
Categories: Pictures
flickr Pictures
Sunday, January 29, 2006
Debbie's Idea
This link from the Freakonomics blog is a site similar to JunkLog, but is hoping to become a reliable reference for people looking for something to read. The key diffenece is that JunkLog is item-based and Debbie's Idea is author-based.
Visit Debbie's Idea.
Categories: books, internet
Long before the Internet was commonly available, Debbie had the idea that it would be useful to have a reference work suggesting which book of an unfamiliar author would be best to read first. Start reading an author with a poor or atypical example of his work, she observed, and you would likely never read that writer again—perhaps losing in the process a world of pleasure and knowledge. On the other hand, since there would seldom be one right book to read first, the resource would have to be a compendium of opinions.
Visit Debbie's Idea.
Categories: books, internet
Wednesday, January 25, 2006
Layout For This Site
I have been working on the layout for this site and using it as a way to learn more about HTML and CSS. The CSS stylesheet for this site now resides on my Geocities website. The links to the Excel Tips and Sheet Music point you to the Geocities site.
I am happy with the site enough to put it out there, but will continue to tweak it. I am interested in comments on the design.
Categories: blogging, internet
I am happy with the site enough to put it out there, but will continue to tweak it. I am interested in comments on the design.
Categories: blogging, internet
Always Check Your Work
I was updating my profile information on JunkLog and accidentally mistyped my address as dailydistraction instead of dailydistractions. Fortunately I checked the link to make sure it worked and found that by leaving the "s" off I was directed to a blog by some guy fantisizing, in some detail, about a girl he works with.
So if for some reason you found your way to that site...sorry. If you link to this site make sure the address is correct. Just remember I am distracted by many things in one day, not just one.
Finally, always check your work. The simpliest mistakes a potentially very embarassing.
Categories: blogging, internet
So if for some reason you found your way to that site...sorry. If you link to this site make sure the address is correct. Just remember I am distracted by many things in one day, not just one.
Finally, always check your work. The simpliest mistakes a potentially very embarassing.
Categories: blogging, internet
Technorati
I registered with Technorati which is a blog search engine. Maybe this will point some people towards this blog. I need to go now and think of some interesting things to write about.
Categories: blogging, internet
Categories: blogging, internet
Tuesday, January 24, 2006
JunkLog
Brad from bradsucks set up a site called JunkLog to keep track of his books, CDs, and DVDs. He has now made it available to everyone. Items are added by seaching the Amazon database. (So if it's not on Amazon you can't add it.) You are then able to add comments, tags, and a rating. There is also a place to indicate that the item is finished (books) and the date finished.
You can see if anyone else has listed an item and look to see what is on their list. Hopefully, you will find some other interesting things you may have never found on your own.
It is modeled after del.icio.us which is designed to help you organize favorites (bookmarks). You can see the items I have listed by visiting my page. I am also going to put a link on the sidebar to my page.
Categories: blogging, internet
You can see if anyone else has listed an item and look to see what is on their list. Hopefully, you will find some other interesting things you may have never found on your own.
It is modeled after del.icio.us which is designed to help you organize favorites (bookmarks). You can see the items I have listed by visiting my page. I am also going to put a link on the sidebar to my page.
Categories: blogging, internet
Subscribe to:
Posts (Atom)
