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
llastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
returnrow = llastrow
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
'Determine last row and last column
lLastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
returncol = lLastCol
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
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.