Excel VBA Basic Training

Excel VBA Basic Training

Creating Macros

How to create a Excel Macro

The easiest way to create a macro is to initially record the actions using the macro recorder.  Macro recorder will create VBA code for every action you take in the spreadsheet.  To record a macro go to View tab -> Macros -> Record Macro.  Give it a name and then perform the actions you want to automate.  Excel will create the VBA code for you.

how to create a excel macro

To view the recorded macro click View Tab->Macros->Select the Macro->Click Edit.  VBA editor will open.  The F8 Key can be used to step through the macro 1 line at a time so you can see what it is doing.

Inherently the macro recorder uses fixed referencing which will need to be changed to be dynamic.  For example if you copy formula down it will only copy the formula for how ever many rows you have.  So if the current dataset has 10 rows the VBA will be hardcoded to copy the formula down 10 rows.  But next time if your dataset has 30 rows the recorded macro will still only copy the formula down 10 rows.  This is an example of fixed referencing.  Macros should be dynamic so the formulas copy down to the bottom of the dataset regardless how long the data is.

To make the VBA dynamic you need to count the number of rows and replace the fixed reference with a dynamic reference.

Example:  Create Macro (43 downloads)

'This multiplication formula will only copy the formula from cell C2 to C9.  But you want it to go C2 to last row.
ActiveCell.Value = "=A2*B2"
Selection.AutoFill Destination:=Range("C2:C9"), Type:=xlFillDefault

'To make it dynamic you need to count the number of rows and replace the fixed reference with the dynamic count.
Dim numberRows As Integer
numberRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.Value = "=A2*B2"
    Selection.AutoFill Destination:=Range("C2:C" & numberRows), Type:=xlFillDefault

After you have created your macro you need to save the file as .xlsm file type.  Which is the macro enabled workbook format.  Anytime you want to run the macro  you just need to have the .xlsm file open.

Once you are comfortable with recording macros and editing them in the VBA editor the next more powerful way of creating macros is writing VBA code.  Google is a great resource for looking up the code you need to perform the action your trying to perform.  For example if you want to download a pdf from a URL you can google search: VBA download pdf from URL.    Then you review the search results and test the code.  You should find something like this https://www.vba-market.com/vba-download-pdf-file-from-url/.  You then test the code to see if it works and integrate it into your macro.

In this example Vba-market provides the following code which will download PDF from URL.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadFile(url As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, url, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

Sub DownloadPDF()
Dim strPDFLink As String
Dim strPDFFile As String
Dim doc, hcol, text As Variant
Dim ie As SHDocVw.InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")

   MsgBox "Select Folder to Save .pdf to)"
Dim FolderName As String
With Application.FileDialog(msoFileDialogFolderPicker)
   .AllowMultiSelect = False

   On Error Resume Next
   strDir = .SelectedItems(1)
   On Error GoTo 0
 End With

    strPDFLink = "https://www.vba-market.com/DownloadPDF_fromURL.pdf"
    strPDFFile = strDir & "\DownloadPDF_fromURL_" & Format(Now, "yyyy.mm.dd") & ".pdf"
    ie.Visible = True
    ie.Navigate (strPDFLink)
    Application.Wait (Now + #12:00:02 AM#)
    Result = DownloadFile(strPDFLink, strPDFFile)

End Sub

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]

Add comment