Excel VBA Macro

This is the reason why the power of Excel when combined with VBA is almost limitless. When we can’t found any Excel function that suite our need, we can built it easily using Excel VBA.
If Excel macro or SUB procedure is easily created using Record Macro button, and usually build to simplify a complex process [...]

Let say we want to distinguish between ordinary cells and cells with formula in it, so we give a special background color to the cells with formula.
For doing it manually is time consuming, especially on Excel Worksheet with large of cells with formula in it (usually a very complex worksheet).
The following vba macro code will [...]

We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel.
Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.
Sub CopyPaste()
Dim InputCells As Excel.Range
Dim [...]

This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.
Sub PersonAge()
Dim iYears As Integer, iMonths [...]

UPDATE June 13, 2008:
Another alternative way to find the last row with data :
Function LastRow() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
LastRow = ix
End Function
I did a simple test, and the function will return the correct last row position [...]

In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not.
The following excel vba function assigned to check whether a workbook we need is open [...]

If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only [...]

Simple Microsoft Excel formula to find the last day of any given month.
‘
=DATE(YEAR(A1),MONTH(A1)+1,0)
‘

The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words.
Function Acronym(Words As Variant) As String
Dim aWord() As String, ix As Integer
aWord = Split(Words.Value, ” “)
For [...]

Ceiling, or changing a specific decimal number into become its upper number, for example if we ceiling 3.25 we will got 4, or if we ceiling 2.75 then we will got 3 as a result.
To get a result like that in Microsoft Excel, we can simply use this very simple but neat Excel VBA function
Function [...]

The function below will allow us to remove a non alphanumeric character from an input string.
Be careful though, lengther the string, more time needed to evaluate each string on the sentence.
To bad that I don’t have any idea on how to use Regular Expression in Excel VBA, if we can use regex to replace [...]

Update:
Sorter version of the VBA function:
Function Number2Char(c As Integer) As String
Number2Char = Split(Cells(1, c).Address, “$”)(1)
End Function
Courtesy of The Plaid Cow.
OR
Function Number2Char(ByVal vNumber)
Dim addr As String
If vNumber 256 Then Exit Function
addr = Range(“A1″).Offset(0, vNumber – 1).Address
Number2Char [...]