Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business
Are you a data enthusiast? Join us for a ride on your data and automate your stuffs

Excel VBA Tips you Must Know – Part 1

Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes. For example, what VBA code you write to add borders in a range?

VBA Tips you Must Know – Part 1

You might be using a code like below:

    Range("A1:D10").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

What if we say that you can achieve the same in just one line of code? Have a look at below code:

    Range("A1:D10").Borders.LineStyle = xlContinuous

In this post, we have shared similar codes that will makes your life easy.

Table of Content

Get File Extension from File Name

The following code returns the extension from any file name:

VBA Tips you Must Know – Part 1

Code:

Right(strFilePath,instr(1,StrReverse(strFilePath),"."))

Example 1:

Right(“E:WorkExcel.Sir.Jitest_file.xlsm”,instr(1,StrReverse(“E:WorkExcelSirJitest_file.xlsm”),”.”))

Result:

.xlsm
Example 2:

Right(“test_file.xlsm”,instr(1,StrReverse(“test_file.xlsm”),”.”))

Result:

.xlsm

Speak from Cell Value

Following code reads the text in the given cell or range. The code can be used where you want to alert the user through system audio device:

VBA Tips you Must Know – Part 1

Codes:

Range("A1").Speak
Cells(1,1).Speak

Protect Excel Sheet for Manual Input but Allow Programming Inputs

This code protects the sheet for manual inputs; however if the changes are done through programming then it will allow the changes.

VBA Tips you Must Know – Part 1

Code:

Sheet1.Protect Password:="123", UserInterfaceOnly:=True

DoEvents

In some situations when you want to accept other actions or interrupts performed by the user then you can make use of DoEvents. For example, you are running a long loop and you want to allow the user to stop the loop in-between.

VBA Tips you Must Know – Part 1
Code:

Public bStop As Boolean
‘This function runs a loop on each row of the sheet. If the Boolean variable is True then it will exit from the loop
Sub StartLoop()
    Dim lCounter As Long
    For lCounter = 1 To Rows.Count
        DoEvents
        If bStop = True Then
            Exit For
        End If
        'Code to be executed for each loop
        UserForm1.Label1.Caption = lCounter
    Next
End Sub
‘This button event will change the Boolean variable to True
Private Sub cmdStop_Click()
    bStop = True
End Sub

Convert an Excel Range to Array

When you want to perform certain tasks on each cell of a large Excel range then a normal loop may take lot of time to complete the task. It would be good idea if you convert the range to an array, perform the required changes and write it back to the Excel range. As the array variable is stored in RAM instead of Hard Disc, the loop will run faster.

VBA Tips you Must Know – Part 1
Code:

Sub RangeToArray()
    Dim varArray() As Variant
    Dim lRow As Long
    Dim lColumn As Long
    'Convert the range to array
    varArray = Range("A1:Z10000")
    For lRow = 1 To 10000
        For lColumn = 1 To 26
            'Perform changes in the array
            varArray(lRow, lColumn) = varArray(lRow, lColumn) + 1
        Next
    Next
    'Write the modified array back to the Excel
    Range("A1:Z10000") = varArray
End Sub

Evaluate a Formula

This function can be used to evaluate formulas or arithmetic calculations directly in the coding. This is helpful when you do not want to perform these calculations on a cell.

VBA Tips you Must Know – Part 1

Code:

Evaluate(“=VLOOKUP(7,A:B,2,0)”)

Example 1:

Evaluate(“=VLOOKUP(7,A:B,2,0)”)

Result:

63
Example 2:

Evaluate(“4/10”)

Result:

0.4

Stop or Start Screen Update

When you are performing multiple changes or navigation in the Excel sheet, Excel application keeps on updating the screen to show the latest changes. This normally slowdowns performance of the code. To solve this problem, you can use ScreenUpdating property of Excel application. When you turn it False then screen will not be updated during code execution. If the property is True then screen will be updated as normal.

Note
This property is set to True automatically once the code execution is completed irrespective of whether you have made it True or False at the end of the code.
VBA Tips you Must Know – Part 1

Code:

Application.ScreenUpdating=False
‘Perform changes in sheet
Application.ScreenUpdating=True

Create a Folder

When you need to create a folder through programming, you can make use of MkDir VBA function for creating a folder.

VBA Tips you Must Know – Part 1

Code:

MkDir (strFolderPath)

Example 1:

The following code will create a folder named “Folder 1” in “E:WorkExcelSirJi” path

MkDir (“E:WorkExcelSirJiFolder 1”)
Example 2:

The following code will create a folder name “Folder 2” in the temp folder which is normally in “C:Users*UserName*AppDataLocalTemp”

MkDir (Environ(“temp”) & “Folder 2”)

Delete a Folder

When you need to delete a folder through programming, you can use RmDir VBA function like below.

VBA Tips you Must Know – Part 1

Code:

RmDir (strFolderPath)

Example:

The following code will delete the folder named “Folder 1” in “E:WorkExcelSirJi” path

RmDir (“E:WorkExcelSirJiFolder 1”)
Note:
The above code is expected to fail if the given folder has any file in it. If you want to delete a folder along with its contents then you can use Kill VBA function followed by RmDir. Have a look at below code:

Code:

Kill ("E:WorkExcelSirJiFolder 1*")
RmDir ("E:WorkExcelSirJiFolder 1")

Rename a File

When you need to rename a file or files then you can make use of Name VBA function to easily rename the file(s). Note that you can also use Name VBA function to move the files from one folder to another. Have a look at the examples mentioned below.

VBA Tips you Must Know – Part 1

Code:

Name strSourceFilePath As strDestinationFilePath

Example 1:

Name “E:WorkExcelSirJiFolder 1Test1.txt” As “E:WorkExcelSirJiFolder 1Test2.txt”
Example 2:

Name “E:WorkExcelSirJiFolder 1Test1.txt” As “E:WorkExcelSirJiTest2.txt”

Thanks for reading the article, subscribe us to get more VBA tricks

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *