9 Use Macros in an Integrated Excel Workbook

You can configure macros that the Oracle Visual Builder Add-in for Excel executes at specific points in the life cycle of an integrated Excel workbook.

Use of this functionality requires you to use the Excel macro-enabled workbook type (.XLSM) and create your macro(s) in a macro module. For more information about creating macros, see Microsoft’s documentation. Describing how to create macros in an Excel workbook is outside the scope of this guide.

Some companies block the usage of Excel macros because they do not think macros are sufficiently secure. Consider your intended user base before you add a macro. After creating a macro, take steps to protect that macro from malicious and/or accidental alterations that might produce unexpected or harmful results. You are responsible for the security risks involved in using macros. Ensure you research this topic before you deliver an integrated workbook to your customers. If a macro that you create results in changes that are incompatible with the add-in or results in undesirable behavior, change the macro to avoid this behavior.

The Layout Designer’s Advanced tab exposes two properties where you can specify macros to execute after download completes (Post-Download Macro) or before an upload begins (Pre-Upload Macro). The macro that you specify for the Post-Download Macro property is not used if the user cancels download, if the table or form is empty, or in the event of an unexpected error. The macro that you specify for the Pre-Upload Macro property is used just prior to upload. If the macro returns any value other than true, the upload operation aborts and a message appears in the Status Viewer to notify the user. If the macro returns true, upload proceeds normally. To return a true or false value from a macro, define a Boolean Function. See Microsoft documentation for details.

Description of the illustration excel-add-macros.png

When an error occurs during the execution of a macro, Excel displays a Microsoft Visual Basic window to the user. We recommend that you implement a robust error handling strategy so that the window displays a useful message to the user who encounters an error during macro execution. The following is a simplistic example. The appropriate error handling strategy for a given macro depends on the logic in the macro.

Sub Refresh()
   
    On Error GoTo ErrHandler:

    ActiveWorkbook.RefreshAll
    Exit Sub
     
ErrHandler:
    Dim failureMessage As String
    failureMessage = Err.Description
    MsgBox "Unable to refresh. Details: " & failureMessage     
    Exit Sub
End Sub

Tip:

The add-in creates and maintains named ranges for the data table. Your macros should never modify these named ranges. However, your macros can access the named range to locate the data table on a dynamic basis, as the following example illustrates:

Sub AddColor()
    On Error GoTo ErrHandler:
    Dim table As Range
    Set table = Sheets("Data").Range("TBL246043480")
    ' The named range, TBL246043480, is managed automatically by the add-in

    Dim cRows As Long
    cRows = table.Rows.Count
    Dim currentTableRow As Long
    Dim amount As Long
    For currentTableRow = 2 To cRows ' start with 2 to skip header row
        amount = table(currentTableRow, 6) ' Amount is sixth column in the table
        If amount < 0 Then
            Debug.Print "Found negative amount = "; amount
            table(currentTableRow, 6).Interior.ColorIndex = 22 ' a light red
        End If
    Next
  
    Exit Sub
     
ErrHandler:
    Dim failureMessage As String
    failureMessage = Err.Description
    MsgBox "Unable to finish adding color. Details: " & failureMessage
     
    Exit Sub
End Sub

Note:

Macro recording is incompatible with add-in features such as download and upload and is not supported. Do not attempt to record any add-in features. In some cases, you may see unexpected exceptions.

Do not leave the Excel Visual Basic editor’s break mode on when you use Download Data or Upload Changes. It can result in an unexpected exception. It is not supported.