The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

Labels: Addins, Formulas, General, Macros, Reference, Shortcuts, Tips, Tutorials

Level talk

1. Close any programs that are running.
   
   
2. Click Start, click Run, type regedit in the Open box, and then click OK.
   
   
3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:
   
   Microsoft Office Excel 2010
   

   HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

   
   Microsoft Office Excel 2007 uses Office\12.0\
   Microsoft Office Excel 2003 uses Office\11.0\
   Microsoft Excel 2002 uses Office\10.0\
   Microsoft Excel 2000 uses Office\9.0\
   
   
4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.
   
   
5. On the Edit menu, click Modify.
   
   
6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.
   
   
7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

Labels: General, Macros, Reference, Tips, Tutorials

Wha's a macro?

"The purpose of this column is simply to provide information to anyone interested in how to make their work life more efficient through the use of macros. When your average computer user finds out that macros are all about little bits of code, the fear in their hearts becomes palpable, all the way from here. And so today I want to ease those fears and invite those of you who've been curious about macros to take the plunge and finally learn about them."

Labels: General, Macros, Reference, Tips, Tutorials

In context

Labels: Customize, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

Revealed

Labels: General, Macros, Reference, Tips

Change tab names automatically

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

Labels: Customize, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

Split between worksheets

Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 'Check for no entry If FileName = "" Then End 'Get Next Available File Handle Number FileNum = FreeFile() 'Open Text File For Input Open FileName For Input As #FileNum 'Turn Screen Updating Off Application.ScreenUpdating = False 'Create A New WorkBook With One Worksheet In It Workbooks.Add template:=xlWorksheet 'Set The Counter to 1 Counter = 1 'Loop Until the End Of File Is Reached Do While Seek(FileNum) <= LOF(FileNum) 'Display Importing Row Number On Status Bar Application.StatusBar = "Importing Row " & _ Counter & " of text file " & FileName 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 1) = "=" Then ActiveCell.Value = "'" & ResultStr Else ActiveCell.Value = ResultStr End If 'For Excel versions before Excel 97, change 65536 to 16384 If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False

End Sub

Labels: General, Macros, Reference, Tips, Tutorials

Count to 52

Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), _
Count:=(52 - Worksheets.Count)
iWeek = 1
For Each sht In Worksheets
sht.Name = "Week " & Format(iWeek, "00")
iWeek = iWeek + 1
Next sht
Application.ScreenUpdating = True
End Sub

Labels: Customize, Macros, Reference, Tips, Tutorials, VBA

So cool!

There are three states that a worksheet can be in; Minimized, Maximized, and Normal.

This macro will gradually resize a worksheet from small to Maximized. The worksheet appears to be growing:

Sub SheetGrow()
Dim x As Integer
With ActiveWindow
.WindowState = xlNormal
.Top = 1
.Left = 1
.Height = 50
.Width = 50

For x = 50 To Application.UsableHeight
.Height = x
Next x

For x = 50 To Application.UsableWidth
.Width = x
Next x

.WindowState = xlMaximized
End With
End Sub

Labels: Customize, General, Macros, Reference, Tips, Tutorials, VBA

No Delimiters

Labels: Formats, Functions, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

Highlight entries

Labels: Formulas, General, Macros, Reference, Tips, Tutorials, VBA

Order tabs

• Sorting Worksheets In Alphabetical Order
• Sorting In Custom Order
• Grouping Sheets By Color

Labels: General, Macros, Reference, Tips, Tutorials, VBA

One day at a time

"Tushar Mehta has a very nice calendar too which can be found here:

Calendar"

"Create a Create a simple calendar for each month or a small calendar (pocket) for a whole year.

The calendar automatically formats some holidays (included Easter Sunday etc.), you might need to customize the workbook to suit your needs."

Labels: General, Macros, Reference, Templates, Tips, Tutorials, VBA

All files in a folder

• The folder name for the listed files
  
• The file names of the files found
  
• The file sizes of the files found
  
• The dates and times of the files found
  
• The total number of files listed

Labels: General, Macros, Reference, Tips, Tutorials, VBA

Roll your own