VBA Tutorial—-Useful VBA Functions which you will need for developing a Excel Macro

Related Articles

VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Office applications such as Microsoft Excel.

By running VBA within the Microsoft Office applications, you can build customized solutions and programs to enhance the capabilities of those applications. A lot of people might not realize that they can actually learn the fundamentals of Visual Basic programming without having a copy of Visual Basic professional. Why? Because there is a built-in Visual Basic Editor in Microsoft Excel, and you can use it to customize and extend the capabilities of MS Excel. The applications you build with MS Excel is called Visual Basic for Applications, or simply VBA.

Here are some of the functions which you must need if you are planning to develop a VBA macro. I have developed these functions myself and I am sharing so that it will prove helpful for you. Also its our first VBA tutorial. Lot more are expected to be in Pipeline.Please hit the facebook like/share button if you like the post.

1) Function to find the last row in an excel

The most common requirement—-you must know the last row in an excel—-No issues just paste the below function before your macro and make a call returnrow(range("a1")) from macro. You will get last row in that excel

Function returnrow(targetcolumn As Range) As Long
Dim llastrow As Long
With targetcolumn.Parent
llastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 End With
 returnrow = llastrow
End Function


2) Function to find the last column in an excel

The second most common requirement is to find the last column in that excel. Following function will help you to do so. Just call returncol(range("a1")). You will get the last column number in your excel.

Function returncol(targetcolumn As Range) As Long

Dim llastrow As Long
Dim lLastCol As Long
With targetcolumn.Parent
    'Determine last row and last column
    lLastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
returncol = lLastCol
End Function

3) Function to convert the column number to column letter

Sometimes we come across a requirement where we need to convert the column number to column letter e.g. the above function will return the results in number. The below function will return the corresponding alphabet e.g. colletter(2) will return "B"

Function ColLetter(ColNumber As Long) As String
Dim s1 As String

    s1 = Left(Cells(1, ColNumber).Address(False, False), _
    1 – (ColNumber > 26))
    ColLetter = s1
End Function


If you have any particular requirement/need help for any code please leave the requirement in comments section. We will try to assist you ASAP. Keep visiting Reckontalk for more VBA tutorials.