Excel VBA Macro

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 directory path + filename of the active file, ex:
D:\My Directory\filename.xls

To simply use it from our formula bar, we can wrap the code into excel vba function like this:

Function FULLFILENAME()

     FULLFILENAME = Application.ActiveWorkbook.FullName

End Function

Every time we needed the information, just call the FULLFILENAME function from the formula bar.

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 all the unwanted character directly, it would be great

Function GETALPHANUMERIC(text)

str_all = "abcdefghijklmnopqrstuvwxyz1234567890"
For lenstr = 1 To Len(text)
    If InStr(str_all, LCase(Mid(text, lenstr, 1))) Then
        GETALPHANUMERIC = GETALPHANUMERIC & Mid(text, lenstr, 1)
    End If
Next

End Function

If somehow what we need is only the apha part without the numeric part or vice versa, simply adjusting the value of str_all variable will solve it.

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 & UCase(Left(aWord(ix), 1))
    Next ix

End Function

Using Split function and space as delimiter, we create an array of word. Then using simple for … to loop, we do looping of each word and take the first letter of each word in array using Left function.

By using iteration and array like this, we will get the acronym of the word in question.

We can directly use the function in Excel formula bar with a reference to the cell that contains the collection of words we want to make an acronym from it as input.

For the details information of each function in use, Split/Ucase/Left, select each function and push F1 while in the Visual Basic Editor.

Simple Microsoft Excel formula to find the last day of any given month.

'
=DATE(YEAR(A1),MONTH(A1)+1,0)
'

Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data.

As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:C15 column no 2 is like this:

=VLOOKUP(A1,B1:C15,2)

With parameters such as the value we looking for, range of table array where we would like to find the value that we want, and how far the column of values that we want from the left most column of the table array range.

Unfortunately, this form is rigid, the table array range is fixed to row 1 until row number 15. So this form is not suitable for doing VLOOKUP on the database which always dynamic, increase or decrease from time to time.

Using the above VLOOKUP form, every time there is an addition of new data, we need to change the VLOOKUP formula to match the numbers of expanding data.

We need to change the format of the VLOOKUP formula to be more flexible and save the time we need to manually adjust the formula by hand.

The following changes is the form that we need:

=VLOOKUP($A1,OFFSET($B$1,0,0,COUNTA($B:$B),3),2)

Here we got help from OFFSET Excel function to return the reference of VLOOKUP table array range that we need plus the COUNTA function to calculate the maximum number of rows on the database.

This way, every time there are any reduction or addition of new data on the VLOOKUP table array, COUNTA will return the number of rows needed, and OFFSET will provide the referense of the new table array, so we do not need to make changes the formula manually

Formula at A2 returning a wrong vlookup value when we use the first vlookup =VLOOKUP(A1,B1:C15,2) formula. This is because the value that we are looking for already outside vlookup table array. Formula at A3 returning the correct value when we use the auto expanding version of the lookup.

Read this great article of Dynamic Ranges for more information about dynamic ranges in Excel.

I am confess, I can not write well, let alone give an easy to read and easy to understand explaination

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 to became one simple push button or call, then an Excel function build to return a value from any kind of calculation and can be use and invoke as a formula.

For example, we can create a simple Excel function to get the last date of the month with date as parameter.

Public Function LASTDATEOFMONTH(ByVal TheDate As Variant) As Date
   Dim nLastDay As Integer
   nLastDay = DatePart("d", DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", _
     -DatePart("d", TheDate) + 1, TheDate))))
   LASTDATEOFMONTH = DateSerial(Year(TheDate), Month(TheDate), nLastDay)
End Function

See the following result.

The result of the formula shows us that the last date of this month is June 30, 2008.

Using Excel function, we can create any kind of specific formula that’s not available in Excel and need to be used for several time on our Excel project.