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

Recent 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.

 








AAP MP Bhagwant Maan at BBC Channel

After rocking parliament with his beautiful and logical poems, Bhagwant Mann has now rocked at BBC Channel. See his full conversation with BBC Anchor...

At the airport (White people vs Brown people)...

Zaid Alit gives us a very hilarious view of how Desi People behave as compared to the white people on the airport http://www.youtube.com/watch?v=0XNb3wHU52I

Mother’s Day Special | Celebrate The Purest Love...

We can keep on talking about the things which a mother does for her children. But we can never be able to completely explain it...

This One Men Country Required a Passport To...

Did you know that in the middle of the desert, there's a 2-acre stretch of land whose owner has declared the Republic of Zaqistan?...

20 Things about Kazakhstan You didn’t Know

Here are the list of 20 funny,amazing & bizarre facts you didn't know about Kazakhstan.   1. There is no institution of ground floor…you are on...

Grouper eats 4ft shark in one bite |...

Goliath grouper eating a black tip shark in one bite off the coast of Bonita Springs Florida. August 2014. https://www.youtube.com/watch?v=O37HI_AX9nY Credit :Gimbb14 Source:Youtube.com