In this page

What is a Visual Basic (VBA) macro?

VBA macros are "little programs" to enhance Excel spreadsheets with dynamic behavior. VBA macros are written in the Visual Basic programming language, and are executed by VBA (Visual Basic for Applications), which is built-in to Excel. Better Excel Exporter for Jira supports VBA macros in the Excel templates, simply by copying the macros from the template to the final output file without modifications.

When should I use VBA macros?

How does this feature relate to Groovy scripting, the other way of adding custom beaviour to Excel exports? Generally speaking, macros are useful when you want to dynamically format, update, automate or post-process an Excel spreadsheet's content after it was exported from Better Excel Exporter. To implement business logic that modify the spreadsheet during the export, use Groovy scripts. In some use cases, you can simply choose the technology you're more familiar with, but here is some general guidance.

When should I use VBA?

  1. When you need to actualize the exported data every time when the spreadsheet is opened. For example, you want to fetch the most recent currency exchange rates from an external web-service, and update sales data with this. Or you work with date and time values, you want your spreadsheet depend on the current time.
  2. When you need to add user interactions to the spreadsheet. Ex: running some logic when the user clicks a button in Excel, or displaying an alert box when the spreadsheet is opened.

When should I use Groovy?

  1. When you need to pre-process the data before that is put to the spreadsheet. Ex: if you want to calculate the average age for open issues, but don't want to dump each issue to Excel to do the calculation there, then do that in Groovy.
  2. When you need to access Jira internals for data to be exported. Ex: running additional JQL queries in Jira, as additional sources of data for the final report.

VBA macro tutorials

There are many great tutorials about Excel visual basic macros in the web. Here is a collection of resources that we found useful:

Using Visual Basic macros with the Better Excel Exporter app

As written above, Better Excel Exporter does not do any sort of processing on the macros, simply copies them from the template spreadsheet to result spreadsheet.

There are some rules, however, to keep in mind when working with macros:

  • You have to use the macro-enabled XLSM format in your templates, not XLSX! (The legacy XLS format would also work, but we generally discourage using that, and prefer XLSM.)
  • If you generate custom filenames in Groovy, then the file extension should be ".xlsm". If the file extension does not match the actual file type, then both your browser and Excel may get confused.
  • Using custom Visual Basic functions or procedures in cell formulas is not supported.

Creating and executing VBA macros

Most typically, you create macros from scratch, by writing VBA code. Alternatively, you can also create "recorded" macros by recording a sequence of manual actions. The nice thing that macros are recorded as VBA code, therefore it is possible to change a recorded macro later!

Macros are executed at certain events, like when a workbook is opened (indirect), or when a button is clicked (direct), or a hot-key is pressed (direct).

Some macro triggering examples:

  • Indirect:
    • A workbook is opened
    • A sheet is changed
    • The window is resized
  • Direct:
    • A button is clicked in a worksheet
    • A hot-key is pressed

Creating a macro-enabled spreadsheet with Better Excel Exporter

Now let's work our way through an example!

We will develop a template which exports the issue key, summary, status, resolution date and due date fields. We will enhance this with a Visual Basic macro to color the cells where the Due Date is before the time when the spreadsheet was opened, and to display an alert box with the count of these overdue issues. Please note that you can't solve this use case in Groovy when the export is created, because the result depends on the time when the spreadsheet is opened in Excel.

Steps:

  1. Open up Excel.
  2. Copy the following content to your Excel workbook: 
    | Key										             | Summary          | Status               | Resolution Date         | Due Date                      |
| <jt:forEach items="${issues}" var="issue">${issue.key} | ${issue.summary} | ${issue.status.name} | ${issue.resolutionDate} | ${issue.dueDate}</jt:forEach> |
  3. Make sure that the Resolution Date and Due Date columns are formatted as a date: right-click them, go to Properties and select your preferred date format.
  4. Check whether the Developer tab is enabled.
  5. Change to the Developer tab, and click Visual Basic.
  6. Double-click the ThisWorkBook object in the left-side tree.
  7. In the top part of the editor, select Workbook and Open.
  8. Copy the macro code to the editor: 
    Private Sub Workbook_Open()

	Dim rowIdx As Long, colIdx As Integer, overdueCount As Integer, lastFilledRow As Long

	lastFilledRow = Range("A1").End(xlDown).Row
	statusColIdx = 3
	resolvedColIdx = 4
	dueDateColIdx = 5

	For rowIdx = 2 To lastFilledRow
		If IsEmpty(Cells(rowIdx, resolvedColIdx)) And Not IsEmpty(Cells(rowIdx, dueDateColIdx)) And Cells(rowIdx, dueDateColIdx).Value < Now() Then
			Cells(rowIdx, dueDateColIdx).Font.Color = -16776961
			overdueCount = overdueCount + 1
		End If
	Next rowIdx

	If overdueCount > 0 Then
		MsgBox overdueCount & " overdue issues found!"
	End If

End Sub
  9. Save your spreadsheet as macro-enabled workbook, with the name vba-macro-enabled-template.xlsm. Again, it must be XLSM!
  10. Login to Jira, go to Administration → Add-ons → Excel Templates and upload vba-macro-enabled-template.xlsm.
  11. Go to Excel Views, and create a new view named "Overdue issues (Excel)" that is allowed for "Issue Navigator" (or "Multiple Issues" in pre-2.2.0 versions) and renders the template you've just uploaded.
  12. Go to Issue Navigator.
  13. Run a JQL query that returns a list of issues where at least one of them is unresolved and overdue (that is due date is before the current date).
  14. Open the Export drop-down and click "Overdue issues (Excel)".
  15. Open up the export in Excel and see the results.

Resources

vba-macro-enabled-template.xlsm — a sample Excel template to create macro-enabled templates. Use this for your experiments.

Questions?

Ask us any time.