Excel VBA Tips you Must Know – Part 1

Excel VBA Tips

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?

Excel VBA Tips

You might be using a code like below:

Excel VBA Master Tool Kit 
    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),"."))

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)”)

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.

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)

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)

Code:

Kill ("E:\Work\ExcelSirJi\Folder 1\*")
RmDir ("E:\Work\ExcelSirJi\Folder 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

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

Similar Posts

Leave a Reply

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

Excel VBA Master Tool Kit