VBA + sample

". . . a new add-in named RDBMail for Excel/Outlook 2007-2010
http://www.rondebruin.nl/mail/add-in.htm

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options.
You have the option to send as workbook or PDF for every mail option."

Labels: Customize, VBA

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Labels: Customize, Formulas, Macros, Reference, Shortcuts, Tips, VBA

You’re not restricted

Labels: Customize, Ribbon, VBA

Also some Windows hints

• Word
• Word VBA
• Excel
• Excel VBA
  
  and
• Windows

Labels: General, Tips, VBA

VBA code

Labels: Reference, Tips, VBA

Fast solution

1. Open any module in the Visual Basic Editor (VBE)
   
2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
   
3. Select the lines of code that you want to turn into comments.
   
4. Click the Comment Block button on the Edit toolbar (it's the sixth button in from the right end of the toolbar).

Labels: VBA

Help topic locations

"Using the Help Context IDs

To make use of this file(s), locate the help topic of interest and make a note of its context ID.

Then, write a VBA subroutine that displays the topic.

You can attach the subroutine to a button, or provide some other way for the user to execute the sub.

For example, if you'd like to display the help topic that show the Date/Time Functions List,
you'll find that the context ID for that particular topic is 5199659.

The following VBA procedure displays that help topic."

Sub ShowHelp()
Application.Help "XLMAIN" & Val(Application.Version) & ".CHM" ,5199659
End Sub

Labels: Reference, Tips, Tutorials, VBA

Declare each one

Labels: VBA

Change tab names automatically

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

Labels: Customize, Formats, Macros, Tips, 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: Macros, Tips, VBA

Download from Microsoft

Labels: VBA

Three is not always enough

Labels: Formats, Tips, Tutorials, VBA

Explicit protection

1. Open a module (start the VBA Editor)
   
   
2. Choose Tools>Options from the menu bar
   
   
3. On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel
   
   
4. Finally, click OK

Labels: 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: Functions, 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)

Also Download Addins for Excel e-mail information

Also see:

John Walkenbach:
Sending Personalized Email from Excel




See all Topics
excel

Labels: Addins, 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, Tips, 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, Tips, VBA

No Delimiters

See:
Date And Time Entry



See all Topics
excel

Labels: Formats, Tips, VBA

Order tabs

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

Here's the code to sort by tab color:

Sub GroupSheetsByColor()
Dim Ndx As Long
Dim Ndx2 As Long
For Ndx = 1 To Worksheets.Count - 1
For Ndx2 = Ndx To Worksheets.Count
If Worksheets(Ndx2).Tab.ColorIndex = _
Worksheets(Ndx).Tab.ColorIndex Then
Worksheets(Ndx2).Move after:=Worksheets(Ndx)
End If
Next Ndx2
Next Ndx
End Sub

Sorting Worksheets In A Workbook

(The colorindex variable chooses one of the 56 colors in Excel's basic palette.
Here are all the colors and numbers as compiled by F. David McRitchie:
Excel Colors )



See all Topics
excel

Labels: General, Tips, VBA

Circular solution

"In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this by storing a running total in a cell comment."

Microsoft Support:
Create a running total in a cell comment

The macro runs each time the value of a cell changes.
It adds the current value of the cell to the value of the cell comment. Then it stores the new total in the cell comment.

I'm sure someone can come up with other uses for this macro.



Also see:

Decision models.com:
Repetitive Calculation Features and Add-Ins

Daily Dose of Excel
Dick Kusleika
Circular References - The Good Kind



See all Topics
excel

Labels: Formulas, Macros, Tutorials, VBA

or Footer

Sub AssignCenterHeaderToValueInA1OnActiveSheet()
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Erlandsen Data Consulting:
Insert headers and footers

Chip Pearson:
Headers and Footers

Jan's Computer literacy 101:
Excel Basics: Setup Header/Footer

University of Wisconsin at Eau Claire:
Modifying Header and Footer Information


See all Topics
excel

Labels: Formats, Macros, Tips, VBA

How to do the impossible (almost)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
   If Target.Value > 10 Then
      MsgBox "Put your macro code here in place of the MsgBox line"
   End If
End If
End Sub

When A1 is changed to a value greater than 10, the macro code will run.

To get to the Worksheet Event code, right-click the sheet tab and choose View Code.




From CPearson.com

Also see:
Change Events

Also:
Microsoft KnowledgeBase:
How to Run a Macro When Certain Cells Change

After posting this, Ross Mclean of Methodsinexcel.co.uk came up with a great work around using a User Defined Function.

Public Function RMAC(ByVal Macro_Name As String,  _

ByVal Arg1 As Variant)

RMAC = Application.Run(Macro_Name, Arg1)

End Function

You can see the simple coding here:

Running a macro from a cell.

Thanks Ross!

Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.

For example (watch line wrap), this VBA code:

Public Function RMAC _
(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run _
(Macro_Name, Arg1)
End Function

Sub MyMacro(arg As String)
ActiveCell.Interior.ColorIndex _
= 3
Beep
End Sub

=rmac("MyMacro","yada")

Labels: Macros, VBA

Security levels

High:

A computer user can open without a prompt a digitally signed project from a trusted publisher. Otherwise, the application blocks opening signed projects from untrusted publishers as well as unsigned projects.

Medium:

A computer user can open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.

Low:

A computer user can open an unsigned project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.

"If you've used Access 2003, you've probably seen several security warning messages - Access 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.

A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped."

Also:
Signing Access 2003 Projects

Other links:

How to make sure that your Office document has a valid digital signature in 2007 Office products and in Office 2003

Also:
HAL-PC MS Office & Excel SIG in Houston, Texas:
Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security



See all Topics
excel

Labels: Macros, VBA

VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

1. Choose a blank worksheet
   
2. In cells A1 and A2, enter the values 1 and 2.
   
3. In B1 and B2, enter Jan and Feb.
   
4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B.

In cell D1 enter the formula

=VLOOKUP(C1,A1:B12,2).

Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name.

To demonstrate the problem, right-click on the heading of column B and choose Insert.

The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0.

Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number.

Press Ctrl-Z to undo the column insertion.

The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references.

You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:

=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

A more esthetically pleasing, or sophisticated, function might be:

COLUMNS(array)

This returns the number of columns in an array or reference.

The modified lookup function looks like this:

=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).


Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

From a PC Magazine article
By Neil J. Rubenking

Also:

Allan Wyatt's ExcelTips:
Using the Column Function

OzGrid:
Copy Rows
(Scroll down to about the middle of the page)



See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials, VBA

Display properties

Sub CommentThem()
 Dim cell As Range
 On Error Resume Next
 Selection.ClearComments
 On Error GoTo 0
 For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
   If cell.Formula <> "" Then
    cell.AddComment
    cell.Comment.Visible = False
    On Error Resume Next 'fails on invalid formula
    cell.Comment.Text Text:=" Address:  " & cell.Address(0, 0) & Chr(10) & _
      " Value:  " & cell.Value & Chr(10) & _
      " Format:  " & cell.NumberFormat & Chr(10) & _
      " Formula: " & cell.Formula
    On Error GoTo 0
   End If
 Next cell

End Sub

by David McRitchie

Also:
Show FORMULA of another cell in Excel

[Edited entry from 3/12/2005]



See all Topics
excel

Labels: Formulas, Tips, VBA

Formatting

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
Next
End Sub

You can change the word CenterFooter to CenterHeader. You could also use LeftHeader, RightHeader, LeftFooter, or RightFooter.

Microsoft KnowledgeBase:
Macro to Change the Date/Time Format in a Header/Footer



See all Topics
excel

Labels: Customize, Formats, VBA

Group sheets

Also:

John Walkenbach:
Making an exact copy of a range of formulas

Microsoft KB:
Run a macro on multiple sheets in Group mode



See all Topics
excel

Labels: General, Tips, Tutorials, VBA

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

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

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

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

Labels: VBA

Context menu redo

For some reason (to be determined later) my shortcut menu started to accumulate a string of "Edit Formula" and "Delete Formula" entries.

They didn't cause a problem, but were distracting.

This little VBA code puts it back to the original settings until I can find a cause.

Sub ResetShortcutMenu()
    Application.CommandBars("Cell").Reset
End Sub

Labels: Tips, 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: VBA

Office Wide

"Automation (formerly known as OLE Automation) is a feature of the Component Object Model (COM), an industry-standard technology that applications use to expose their objects, methods, and properties to development tools, macro languages, and other applications.

For example, a spreadsheet application might expose a worksheet, chart, cell, or range of cells--each as a different type of object. A word processor might expose objects such as an application, document, paragraph, bookmark, or sentence.

When an application supports Automation, the objects that the application exposes can be accessed through Visual Basic. You can use Visual Basic to manipulate the objects by invoking methods or by getting and setting properties of the objects."

Here's an example:

Inserting Data into a Microsoft Word Document

With Automation code, you can open a Microsoft Word document and move to a bookmark location in the document. The following example opens a Microsoft Word document and inserts text after a bookmark.

This example assumes that you have Microsoft Word on your computer, that you have an existing document called C:\My Documents\WordTest.doc, and that the document contains a pre-defined bookmark named City.

Sub FindBMark()

   Dim wordApp As Word.Application
   Dim wordDoc As Word.Document
   Dim wordRange As Word.Range

   Set wordApp = CreateObject("Word.Application")
   Set wordDoc = wordApp.Documents.Open("C:\My Documents\Wordtest.doc")

   wordApp.Visible = True

   ' Go to the bookmark named "City".
   Set wordRange = wordDoc.Goto(What:=wdGoToBookmark, Name:="City")
   wordRange.InsertAfter "Los Angeles"

   ' Print the document.
   wordDoc.PrintOut Background:=False

   ' Save the modified document.
   wordDoc.Save

   ' Quit Word without saving changes to the document.
   wordApp.Quit SaveChanges:=wdDoNotSaveChanges

   Set wordApp = Nothing

End Sub

Microsoft Support provides an entire Help file to assist you. It includes theory and examples.

The file is called XPAutomation.chm.

Download it and then double click on the file to run it. You could also set up a shortcut on the desk top, if it will be used frequently.


Microsoft Knowledge Base Article: 302460

This was aimed at Office 2002 but it can be used with later versions:

• Microsoft Access
  
• Microsoft Excel
  
• Microsoft Outlook
  
• Microsoft PowerPoint
  
• Microsoft Word

Labels: VBA

Realign a bunch

Labels: VBA

Cardinal numbers

From the Microsoft Knowledgebase collection:


How to Convert a Numeric Value into English Words - 213360

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:

=SpellNumber(32.50)


Also:

From John Walkenbach:

BAHTTEXT?
If you use Excel 2002+, you may have discovered the new BAHTTEXT worksheet function. This function converts a number to Thai text, and adds a suffix of "Baht".
This is the only built in function that translates numbers to words and then only in Thai.

For Access see:
Access- Numbers to Words


To create cardinal numbers in Word, see:
Word - Numbers to Words



See all Topics
excel

Labels: Functions, VBA

Programmatically pick cells

Microsoft has provided 22 ways to select cells/ranges by using Visual Basic procedures in Excel.

Here are a few of the subjects covered:

• 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

How to select cells/ranges by using Visual Basic procedures

Dick Kusleika has some comments on the coding:
How to Spread Bad Habits



See all Topics
excel

Labels: VBA

Edit toolbar

You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.

To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

Ross, suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.



See all Topics
excel

Labels: Macros, VBA