RD PH/Cloud Info: Useful Macros

Summary

This article discusses the attached 5 commonly used Macros. Many of our customers find these Macros handy to manipulate data in a certain way. They utilize a feature in the application called "Sub OSR_ReportComplete()" which allows for a macro to run after data has been populated, saving the user valuable time when running and editing reports.'

Article sections

• More Information
• References

More Information

The attached macros are:

• Page Break – Adds page break where specified by a user.
• Hide 0 Rows – Hides rows whose values are 0. Sometimes unchecking “Display Blank Records” doesn’t work.
• Macro to hide blank tabs – This works to remove tabs with no data when using “Sheet per Value”. There is no built-in feature to suppress tabs with no data.
• Concatenation Custom Function – adding the custom function will allow you to use the “=Concat(Cell_Range, separator)” function in Excel where the values within the cell range will be concatenated together. See the below image for example. If the “separator” is omitted, it will use a space between the values in each cell.
• Hide 0 Columns - Hides columns whose values are 0.

Note (For 4x Publisher)

1. If you plan on using Publisher to distribute your reports, you will need to add a delay trigger to the macro. The code is Application.Wait (Now + TimeValue("0:01:00")). The TimeValue should be determined by running the report in Excel and then adding 30 seconds to the final rendering time. A sample of the code could be:

   Sub OSR_ReportComplete

   Application.Wait (Now + TimeValue("0:01:00"))

   End Sub

To view these macros, open one of the files and click Alt+F11 (or click VBA editor from the Developer ribbon).

References:

To add the macros in these reports to your own, go through the steps below.

1. Save your report as an Excel macro-enabled workbook
   • Select File and Save As
   • Choose the location you want to save your file
   • Fill out File Name
   • Hit the drop-down for Save as type and choose Excel macro-enabled workbook
   • Select Save
2. Add the Developer tab in Excel if it does not already show up.
   
   • Select File
   • Select Options
   • Choose Customize Ribbon
   • Hit the drop-down for Choose commands from and select Main Tabs
   • Highlight Developer and hit Add
   • Select OK
3. Add the Macro
   • Open the report with the Solver macro along with your other report
   • Choose the Developer tab on the report with the preexisting macro
   • Hit the Macros option
   • Highlight the macro you want to add to your report and hit Edit
   • Copy the entire macro text
   • Expand on VBAProject and Microsoft Excel Objects for your report
   • Double click on ThisWorkbook and paste the macro text in the box 
   • Hit the save icon and close out of the macro editor

Every macro has a short set of directions on how to get it to work for your report. After you have completed these steps the macro will be executed after you run your report automatically.