An easier read

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

MsgBox "Hello World!", , "My Message Box"

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

Labels: VBA

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

• Update Word Document Statistics in the Title Bar
  
• Create Outlook Rules Programmatically
  
• Delete Repeated Text Throughout a Word Document
  
• Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  
• Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  
• Display Reminder Information When a User Opens an Office Document
  
• Synchronize an Access Main Form to a Subform and Vice Versa
  
• Log Worksheet Changes to an XML File
  
• Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  
• Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Labels: General, Tips, Tutorials, VBA

Realign a bunch

Labels: General, Reference, Tips, Tutorials, VBA

Programmatically pick cells

• How to Select a Cell on the Active Worksheet
  
  
• How to Select a Cell on Another Worksheet in the Same Workbook
  
  
• How to Select a Range of Cells on the Active Worksheet
  
• How to Select a Named Range on a Worksheet in a Different Workbook
  
  
• How to Select a Cell Relative to the Active Cell
  
  
• How to Select the Union of Two or More Specified Ranges
  
  
• How to Select the Intersection of Two or More Specified Ranges
  
  
• How to Select the Last Cell of a Column of Contiguous Data
  
  
• How to Select the Blank Cell at Bottom of a Column of Contiguous Data

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

In context

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

VBA code

Labels: General, Reference, Tips, Tutorials, VBA

User Function

=Row()

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

=ADDRESS(row_num,column_num,abs_num)

SUBSTITUTE(text,old_text,new_text)

"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."

Labels: Functions, General, Reference, Shortcuts, Tips, Tutorials, VBA

How long is a month?

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

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

Change tab names automatically

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

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

From Microsoft

Labels: VBA

Three is not always enough

Labels: Customize, Formats, Formulas, General, Reference, Shortcuts, Tips, Tutorials, VBA

I bid 3 Red

What if you would like to know the color name or to count or to sum cells by a fill color? There is no built-in function in Excel.

In this case you can make a User Defined Function (UDF).

Here is the sample UDF that you can use to analyze, count and sum the cells depending on their filled color.

These UDF function can be used in the same way as built-in functions that you can use in the worksheet.

• AnalyzeColor Returns the color name, the color index or color index in RGB.
  Syntax: AnalyzeColor(color range, optional; "text" or "index" or "rgb". When it is omitted "text" is used.)
  
  
  
• CountColor Counts the number of cells depending on their filled color.
  Syntax: CountColor(color range, target range)
  
  
  
• SumColor Adds all the numbers in a range of cells depending on their filled color.
  Syntax: SumColor(color range, target range)

Labels: Formulas, Functions, Reference, Tips, Tutorials, VBA

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

Outlook, Excel, and VBA

• Mail Workbook
• Mail one Sheet
• Mail more than one Sheet
• Mail the Selection or range
• Mail Every Worksheet with Address in cell A1
• Mail sheet or sheets to one or more people
• Mail range or sheet in the body of the mail (Send personalized email)
• Mail a message to each person in a range with Outlook
• Mail a message to each person in a range with CDO (no security warnings)
• Sending a different file to each person in a range with Outlook
• Zip the ActiveWorkbook and mail it with Outlook
• Security (Prevent displaying the dialog to Send or not Send)

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

Folder size list

1. Copy the code and open Excel.
   
2. Press Alt+F11 and, if necessary, on the Visual Basic Editor menu, Insert>Module
   
3. Paste the code.
   
4. You could use Alt+Tab to bring the worksheet forward.
   
5. Go to Tools>Macros and run the Macro named "GetFolderListing".

Labels: General, 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