12 good to know tips in Excel VBA

1. Copy file from one location to other using Excel VBA code

You can use this code to copy paste the file from one destination to other

e.g. if you require to take backups of the file on daily basis.

Sub Copy_File()
' Declare variables
    Dim filename As String, Source As String, Destination As String

'Set values
    filename = "abc"
    Source = "C:\Test\Desert.jpg"
    Destination = "C:\Test\Backup\" & filename & ".jpg"

'File copy code
    FileCopy Source, Destination
    
End Sub



2. Copy and Save file with Date Time Stamp using Excel VBA

You can use below code to copy and save the file with the date and time stamp. This code is same as one of the Copy file code in my previous blog, Just check the changes in the code for Destination.

Sub Copy_File_dt ()

' Declare variables
    Dim filename As String, Source As String, Destination As String

'Set values
    filename = "abc_"
    Source = "C:\Test\Desert.jpg"
    Destination = "C:\Test\Backup\" & filename & Format(Now(), "mm_dd_yyyy hh mm AMPM") & ".jpg"

'File copy code
    FileCopy Source, Destination
    
End Sub


3. Remove hyperlinks from Excel, using VBA code

There is a simple one line code to do this. Copy paste below VBA code in excel VBA window and run. All hyperlinks from the active sheet will be removed.

Sub removeallhyperlinks()

ActiveSheet.Hyperlinks.Delete

End Sub



you can also use below code:

Sub removeallhyperlinks()

Cells.Hyperlinks.Delete

End Sub



4. Resize all charts to same size using Excel VBA

Below code is used to re-size all the charts to same size from the the active sheet. Copy paste below code in your excel VBA window and run the macro to resize the charts. 

Sub ResizeCharts()

' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject

For Each chart In ActiveSheet.ChartObjects

        With chart.Parent

' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.        
        chart.Height = Application.InchesToPoints(5)
        chart.Width = Application.InchesToPoints(10)
        
    End With

Next

MsgBox "All charts in the active sheet are resized !"

End Sub


In above code we are using inches as the measure to change the chart size. Similarly we can use centimeters to re-size the charts.

Sub ResizeCharts()

' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject

For Each chart In ActiveSheet.ChartObjects

        With chart.Parent

' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.        
        chart.Height = Application.CentimetersToPoints(5)
        chart.Width = Application.CentimetersToPoints(10)
        
    End With

Next

MsgBox "All charts in the active sheet are resized !"

End Sub


If you observe in the above code only two lines are changed.

   chart.Height = Application.CentimetersToPoints(5)
   chart.Width = Application.CentimetersToPoints(10)


5. How to deactivate Alerts while running macro in Excel VBA

You can deactivate the alerts received while running the VBA macro in excel, using below code -

Application.DisplayAlerts = False

You can also deactivate the screen updating while running the macro by using the below code -

Application.ScreenUpdating = False

If your macro is moving from different tabs of worksheet or excel files, the screen will show the details of the macro's step. To disable this we can use the above code.

Below is one of the example, on how this codes can be used -

Sub deactivateAlerts()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Sheet1").Select
ActiveSheet.Hyperlinks.Delete

Sheets("Sheet2").Select
ActiveSheet.Hyperlinks.Delete

Sheets("Sheet3").Select
ActiveSheet.Hyperlinks.Delete

Sheets("Sheet4").Select
ActiveSheet.Hyperlinks.Delete

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Your macro is completed !"

End Sub


The simple code above used, to delete all the hyperlinks in 4 different sheets from your workbook. When the macro is running without the displayalerts = false and screenupdating = false, it will show the steps of macro on screen. 

When the displayalerts = false and screenupdating = false is used, you will see the same screen where you are running the macro and you will directly receive the message box stating "Your macro is completed !"

Don't forget to add the below lines before End Sub of your code.

Application.DisplayAlerts = True
Application.ScreenUpdating = True

These lines will reset the Alerts and Screen updating.


6. How to Open, Save As and Replace file using Excel VBA macro

Below is the macro which is used to Open, Save As and Replace the abc.xls file saved on location - C:\Users\Public\Documents\abc.xls


Sub ReplaceRename()

' Disable alerts
Application.DisplayAlerts = False

'Open file
Workbooks.Open filename:= _
      "C:\Users\Public\Documents\abc.xls"

'Save As and replace or Rename file. In below case we are saving the .xls file in .xlsx format. In case you need to rename the file, just change the abc.xlsx to newname.xlsx in below code
ActiveWorkbook.SaveAs filename:= _
       "C:\Users\Public\Documents\abc.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

'Close workbook
ActiveWindow.Close

'Reset alerts
Application.DisplayAlerts = True

'Message pop-up
Msgbox "Macro completed successfully !"

'Exit Macro
End Sub


If we run the macro with the removing the display alerts code on line 2 of code, then we will receive below pop-up message. With the above code we will receive only below message - 

"Macro completed successfully !"



Above code is used only in case you are using excel file and need to convert the format from .xls to .xlsx or any other excel formats.

To replace or rename any other file format you can use the below code -

Sub RepRenanyfile()


'FileCopy Source, Destination
FileCopy "C:\Users\Public\Documents\abc.xls", "C:\Users\Public\Documents\def.xlsx"

MsgBox "Macro completed successfully !"

End Sub


7. How to create a function in excel to get the logged in USER Name

Below is the simple function which is used to get the logged in user name

Function GetUserName()
    GetUserName = Environ("UserName")
End Function


Once the above function is created in the module, you can write a formula in any cell in excel cell to get the output as logged in user's name -
=GetUserName()

Same macro can be used to give a popup message with the User name, with Msgbox code addition -

Function GetUserName()
    GetUserName = Environ("UserName")
    MsgBox "Logged in User Name is " & GetUserName
End Function


8. How to execute excel VBA macro on change of specific cell in excel sheet ?

Please follow the below steps to execute the task -

1. We need to go to Excel VBA editor by pressing ALT + F11
2. Double click on sheet which contains our target cell e.g. Sheet1
3. Add the below code to the sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
Call Macro1
End If
End Sub

4. If any value in cell E3 of Sheet1 is updated or changed the Macro1 will be executed

5. Insert new module and Add the below code to with name Macro1 

Sub Macro1()

MsgBox "The value entered in Range E3 is " & Range("E3").Value

End Sub

6. Now try changing the value in cell E3 of Sheet1, the Macro1 will be executed and you will get a messagebox pop-up window

9. How to select next column or row using excel VBA ?

Below are the codes using which you can move the active selection of cell to the Left, Right, Up or Down.

The code is -
Activecell.Offset(0,0)

The (0,0) in brackets represent the row and column.

Copy and paste the below code in your excel VBA window to test the working of the Offset code.


Sub TestOffset()

' Here the cursor on active sheet moves to next row (moves once), no change in column
ActiveCell.Offset(1, 0).Select 

' Here the cursor on active sheet moves to next column (moves once), no change in row
ActiveCell.Offset(0, 1).Select

' Here the cursor on active sheet moves to next row and next column
ActiveCell.Offset(1, 1).Select

' Here the cursor on active sheet moves to upper row (moves once), no change in column
ActiveCell.Offset(-1, 0).Select

' Here the cursor on active sheet moves to previous column (moves once), no change in row
ActiveCell.Offset(0, -1).Select

' Here the cursor on active sheet moves to upper row and previous column
ActiveCell.Offset(-1, -1).Select

'Using the above codes, you can select the columns or rows you need from activecell. Below are some examples -
ActiveCell.Offset(2, 0).Select
ActiveCell.Offset(-1, 2).Select
ActiveCell.Offset(2, 3).Select

End Sub



10. How to Resize and Reset the Comments in Excel using VBA code ?

Below code can be used to reset all comments to default location and size.

Copy paste the below code to any of the Excel VBA module and run it on the active sheet from which you need to reset the comments.

Sub resetcomments()
On Error Resume Next
Application.ScreenUpdating = False
Set allCommentsr = Range("A1").SpecialCells(xlCellTypeComments)

If allCommentsr Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Out

For Each r In allCommentsr
With r.Comment
.Shape.Height = 55   'you can change the number you need to resize
.Shape.Width = 96    'you can change the number you need to resize
End With

r.Comment.Visible = True
r.Comment.Shape.Select True

With Selection
.Interior.ColorIndex = 19   'you can change the colorindex number for using different color
.Font.Bold = False            'you can change to True if you need the text in BOLD
.Font.Size = 8                  'you can change the number for font size you need
End With

r.Comment.Visible = False
Next r

Out:
Set allCommentsr = Nothing

MsgBox "All comments are resized and restored to default", vbInformation

Application.ScreenUpdating = True
End Sub


11. Add Start and End row to run the macro, in Excel VBA

Below code can be used to enter the start and end rows to run the macro for specific code. In the below example we have used the Msgbox code to display, the value in the specific cell when the macro is executed.

Sub testrow()
    
    Do While True
        line1 = InputBox("ENTER FIRST ROW NUMBER ")
        line1 = LTrim(RTrim(line1))
        If line1 <> " " Then
            Exit Do
         End If
    Loop

    Do While True
        line2 = InputBox("ENTER THE LAST ROW NUMBER ")
        line2 = LTrim(RTrim(line2))
        If line2 <> " " And line2 > 1 Then
            Exit Do
         End If
    Loop

    For irow = line1 To line2
    icol = 1
    
    a = Cells(irow, icol).Value
    
    MsgBox a ' You can change this line to call your macro or update any other code

    Next
    MsgBox "completed"
End Sub


12. How to filter the unwanted fields using Auto filter in Excel VBA ?

In below example, there are name of Subjects in column D. Say, we need to delete all rows except Hindi and Marathi subject using excel VBA code.

We can use the below code to execute the deletion task using VBA code. 

Sub Keep_HM()

'Deselect the subjects Hindi and Marathi
ActiveSheet.range("D:D").AutoFilter Field:=1, Criteria1:="<>HINDI", _
Operator:=xlAnd, Criteria2:="<>MARATHI"

'Delete the filtered cells
ActiveSheet.range("D1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

'Remove the filter from active sheet
ActiveSheet.AutoFilterMode = False

'Message box pop-up after execution
Msgbox "All subjects except, HINDI and MARATHI are deleted", vbInformation

End Sub




*all codes in this blog are tested on Excel 2010

Use of Select Case & If Else statements in Excel VBA

Below is the small program created to view your personality based on your Date of Birth. Copy paste the below code in your excel VBA editor, Run the code and check out your personality :)

Code:

Sub checkpersonality()

Dim bdate As Integer, bmonth As Integer, byear As Integer, total As Integer
Dim calca As Integer, calcb As Integer, calcc As Integer, calcd As Integer, calctotal As Integer
Dim calce As Integer, calcf As Integer, yourbnum As Integer

On Error GoTo smile_error

bdate = InputBox("Enter your Day of birth:")
bmonth = InputBox("Enter your Month of birth:")
byear = InputBox("Enter your Year of birth:")

total = bdate + bmonth + byear

calca = Left(total, 1)
calcb = Right(Left(total, 2), 1)
calcc = Right(Left(total, 3), 1)
calcd = Right(total, 1)

calctotal = calca + calcb + calcc + calcd

If calctotal >= 10 Then    calce = Left(calctotal, 1)
    calcf = Right(calctotal, 1)
    yourbnum = calce + calcf
Else    yourbnum = calctotal
End If

Select Case yourbnum

Case 1
  MsgBox "Your Birth number is " & yourbnum & " and You are The originator" & vbCrLf & vbCrLf & "1s are originals. Coming up with new ideas and executing them is natural. Having things their own way is another trait that gets them as being stubborn and arrogant. 1s are extremely honest and do well to learn some diplomacy skills. They like to take the initiative and are often leaders or bosses, as they like to be the best. Being self-employed is definitely helpful for them." & vbCrLf & vbCrLf & "Lessons to learn: Others ideas might be just as good or better and to stay open minded." & vbCrLf & vbCrLf & _

         "Famous 1s: Tom Hanks, Robert Redford, Hulk Hogan, Carol Burnett, Wynona Judd, Nancy Reagan, Raquel Welch, Prassann Daphal", vbInformation

Case 2
  MsgBox "Your Birth number is " & yourbnum & " and You are The peacemaker" & vbCrLf & vbCrLf & "2s are the born diplomats. They are aware of others needs and moods and often think of others before themselves. Naturally analytical and very intuitive they don't like to be alone. Friendship and companionship is very important and can lead them to be successful in life, but on the other hand they'd rather be alone than be in an uncomfortable relationship. Being naturally shy they should learn to boost their self-esteem and express themselves freely and seize the moment and not put things off." & vbCrLf & vbCrLf & _

         "Famous 2s: President Bill Clinton, Madonna, Whoopi Goldberg, Thomas Edison, Wolfgang Amadeus Mozart", vbInformation

Case 3
  MsgBox "Your Birth number is " & yourbnum & " and You are The life of the party" & vbCrLf & vbCrLf & "3s are idealists. They are very creative, social, charming, romantic, and easygoing. They start many things, but don't always see them through. They like others to be happy and go to great lengths to achieve it. They are very popular and idealistic. They should learn to see the world from a more realistic point of view." & vbCrLf & vbCrLf & _

         "Famous 3s: Alan Alda, Ann Landers, Bill Cosby, Melanie Griffith, Salvador Dali, Jodi Foster", vbInformation

Case 4
  MsgBox "Your Birth number is " & yourbnum & " and You are The conservative" & vbCrLf & vbCrLf & "4s are sensible and traditional. They like order and routine. They only act when they fully understand what they are expected to do. They like getting their hands dirty and working hard. They are attracted to the outdoors and feel an affinity with nature. They are prepared to wait and can be stubborn and persistent. They should learn to be more flexible and to be nice to themselves." & vbCrLf & vbCrLf & _

         "Famous 4s: Neil Diamond, Margaret Thatcher, Arnold Schwarzenegger, Tina Turner, Paul Hogan, Oprah Winfrey", vbInformation

Case 5
  MsgBox "Your Birth number is " & yourbnum & " and You are The non-conformist" & vbCrLf & vbCrLf & "5s are the explorers. Their natural curiosity, risk taking, and enthusiasm often land them in hot water. They need diversity, and don't like to be stuck in a rut. The whole world is their school and they see a learning possibility in every situation. The questions never stop. They are well advised to look before they take action and make sure they have all the facts before jumping to conclusions." & vbCrLf & vbCrLf & _

         "Famous 5s: Abraham Lincoln, Charlotte Bronte, Jessica Walter, Vincent Van Gogh, Bette Midler, Helen Keller, Mark Hamil.", vbInformation

Case 6
  MsgBox "Your Birth number is " & yourbnum & " and You are The romantic" & vbCrLf & vbCrLf & "6s are idealistic and need to feel useful to be happy. A strong family connection is important to them. Their actions influence their decisions. They have a strong urge to take care of others and to help. They are very loyal and make great teachers. They like art or music. They make loyal friends who take the friendship seriously. 6s should learn to differentiate between what they can change and what they cannot." & vbCrLf & vbCrLf & _

         "Famous 6s: Albert Einstein, Jane Seymour, John Denver, Meryl Streep, Christopher Columbus, Goldie Hawn", vbInformation

Case 7
  MsgBox "Your Birth number is " & yourbnum & " and You are The intellectual" & vbCrLf & vbCrLf & "7s are the searchers. Always probing for hidden information they find it difficult to accept things at face value. Emotions don't sway their decisions. Questioning everything in life, they don't like to be questioned themselves. They're never off to a fast start, and their motto is slow and steady wins the race. They come across as philosophers and being very knowledgeable, and sometimes as loners. They are technically inclined and make great researchers uncovering information. They like secrets. They live in their own world and should learn what is acceptable and what not in the world at large." & vbCrLf & vbCrLf & _

         "Famous 7s: William Shakespeare, Lucille Ball, Michael Jackson, Joan Baez, Princess Diana", vbInformation

Case 8
  MsgBox "Your Birth number is " & yourbnum & " and You are The big shot" & vbCrLf & vbCrLf & "8s are the problem solvers. They are professional, blunt and to the point, have good judgment and are decisive. They have grand plans and like to live the good life. They take charge of people. They view people objectively. They let you know in no uncertain terms that they are the boss. They should learn to exude their decisions on their own needs rather than on what others want." & vbCrLf & vbCrLf & _

         "Famous 8s: Edgar Cayce, Barbra Streisand, George Harrison, Jane Fonda, Pablo Picasso, Aretha Franklin", vbInformation

Case 9
  MsgBox "Your Birth number is " & yourbnum & " and You are The performer" & vbCrLf & vbCrLf & "9s are natural entertainers. They are very caring and generous, giving away their last dollar to help. With their charm, they have no problem making friends and nobody is a stranger to them. They have so many different personalities that people around them have a hard time understanding them. They are like chameleons, ever changing and blending in. They have tremendous luck, but also can suffer from extremes in fortune and mood. To be successful, they need to build a loving foundation." & vbCrLf & vbCrLf & _

         "Famous 9s: Albert Schweitzer, Shirley McLaine, Harrison Ford, Jimmy Carter, Elvis Presley", vbInformation

Case Else
  MsgBox "I didn't knew this, You are not HUMAN !", vbCritical
End Select
Exit Sub

smile_error:
MsgBox "Idiot you entered something wrong !" & vbCrLf & vbCrLf & "You don't remember your Birth Date ?", vbCritical

End Sub

Input :

Output:

Done

How to write a For Next Loop in Excel VBA ?

Below is the simple code for, loop used to give a pop-up message for numbers 1 to 10


Sub ForLoop()

For n = 1 To 10
    MsgBox n
Next

End Sub

How does this work?

Sub ForLoop()      ' Declaring Name of the Macro

For n = 1 To 10    ' Setting the variable and assigning the values to run the loop

    MsgBox n        ' Message box pop-up showing the value assigned to the variable

Next                  ' Next will take us to the Msgbox step, as the value of n is not yet 10

End Sub              ' End of the macro once the value of n is set to 10

Actual Screenshot of Code written in Excel VBA

Find Even Odd number using For Next Loop

below code is used to find the Even or Odd number:

Sub EvenOdd()
For n = 1 To 10
        If n Mod 2 = 0 Then
        MsgBox n & " is Even Number"
       Else
        MsgBox n & " is Odd Number"
    End If
Next n
End Sub



How does this work ?

' Declaring Name of the Macro
Sub EvenOdd()   

' Setting the variable and assigning the values to run the loop
For n = 1 To 10
    
' Here we are checking the simple logic of Math's, using which we are checking if the number is divisible by 2. If the result of modulus is 0 it means that number can be divided by 2 and is Even Number. Similarly if the modulus is not exactly 0, this means the number is not exactly divisible by 2, hence it will be Odd number.
    If n Mod 2 = 0 Then
    
'If modulus output is 0
    MsgBox n & " is Even Number"
    

    Else
  
'If modulus output is not exactly zero  
    MsgBox n & " is Odd Number"



'End of If Statement
    End If

' Next will take us to the Msgbox step, as the value of n is not yet 10
Next n           

' End of the macro once the value of n is set to 10

End Sub


Actual screenshot of the Excel VBA code: