Excel VBA Macro

How to Get a Full File Path in Excel?

Admin — Wed, 19 May 2010 13:24:29 +0000

Ever needed to find out the full path of your current file? What I mean by full path here is the full location of the active file including the directory path and full file name. If you did, then what you need is this following code: Application.ActiveWorkbook.FullName The one line code will return the full [...]

Post from Excel VBA Macro

How to Get a Full File Path in Excel?

Remove Non-AlphaNumeric Characters from String

Admin — Tue, 18 May 2010 13:11:27 +0000

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 [...]

Post from Excel VBA Macro

Remove Non-AlphaNumeric Characters from String

Sum Unique/Distinct Values in Excel

Admin — Tue, 20 Oct 2009 07:29:05 +0000

Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only. Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we [...]

Post from Excel VBA Macro

Sum Unique/Distinct Values in Excel

Excel Extract Cell Comments

Admin — Fri, 19 Jun 2009 07:11:52 +0000

Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary() Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As [...]

Post from Excel VBA Macro

Excel Extract Cell Comments

How to get an Acronym using Excel VBA?

Admin — Wed, 17 Jun 2009 07:25:04 +0000

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 ix = 0 To UBound(aWord) Acronym = Acronym [...]

Post from Excel VBA Macro

How to get an Acronym using Excel VBA?

Get Position of Last Column Containing Data with Excel VBA

Admin — Sat, 13 Jun 2009 03:38:28 +0000

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 [...]

Post from Excel VBA Macro

Get Position of Last Column Containing Data with Excel VBA

Find the Last Day of any Month

Admin — Tue, 09 Jun 2009 03:42:11 +0000

Simple Microsoft Excel formula to find the last day of any given month. ' =DATE(YEAR(A1),MONTH(A1)+1,0) ' Post from Excel VBA MacroFind the Last Day of any Month

Post from Excel VBA Macro

Find the Last Day of any Month

Schedules to Refresh Data Automatically using VBA Macro

Admin — Mon, 08 Jun 2009 16:02:37 +0000

I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook. Below is a sample of how we can do this in Microsoft Excel *just an outline*. The logic is simple, when we open [...]

Post from Excel VBA Macro

Schedules to Refresh Data Automatically using VBA Macro

Excel VBA Add Set of Worksheets Automatically

Admin — Mon, 08 Jun 2009 02:53:14 +0000

There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base. Here a simple example of [...]

Post from Excel VBA Macro

Excel VBA Add Set of Worksheets Automatically

Auto Format Excel Cells with Error Value

Admin — Tue, 02 Jun 2009 16:38:18 +0000

With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells. In this example, background color of the cell that contains the error value will [...]

Post from Excel VBA Macro

Auto Format Excel Cells with Error Value