Using VBA to improve the functioning of OUTREG2

Today, let met show you how to use VBA to improve the functioning of OUTREG2 created by Roy Wada, a very nice package to produce estimate tables after estimations with Stata. This is a quite old package, but still at the second place of the most downloaded package at SSC in February 2025. I will provide two improvements: the choice of the font and the autofit for the columns. Let me add that I used ChatGPT 4o for the VBA part.

The first step consists in creating an Excel complement: open Excel and use ALT + F11 to open VBA in Excel.

Rename the Module as ‘EventHandler’ in the left-bottom window and paste the following VBA code:

Option Explicit

Public WithEvents App As Application

' Appliquer AutoFit + Times New Roman pour les nouveaux fichiers
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Wb.Sheets
        ws.Cells.EntireColumn.AutoFit
        ws.Cells.Font.Name = "Times New Roman"
    Next ws
    Application.ScreenUpdating = True
End Sub

' Appliquer AutoFit + Times New Roman lorsqu'un classeur existant est ouvert
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Wb.Sheets
        ws.Cells.EntireColumn.AutoFit
        ws.Cells.Font.Name = "Times New Roman"
    Next ws
    Application.ScreenUpdating = True
End Sub

' Appliquer AutoFit + Times New Roman quand on active une feuille
Private Sub App_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    Sh.Cells.EntireColumn.AutoFit
    Sh.Cells.Font.Name = "Times New Roman"
    Application.ScreenUpdating = True
End Sub

Now, I will link the ‘Event Handler’ to ‘ThisWorkbook’:

Option Explicit

Dim XlEvents As New EventHandler

Private Sub Workbook_Open()
    Set XlEvents.App = Application
End Sub

Now, I have to save the Excel complement. Go to File > Save as and choose “Excel complement”. Name the file AutoFit_AddIn.xlam. Then, save it in this folder:

C:\Users\VotreNomUtilisateur\AppData\Roaming\Microsoft\AddIns

Change ‘VotreNomUtilisateur’ with your own user name. Activate the Excel complement, go to File > Options > Complements. Then, go to manage and select “Excel complement” and click on “Reach”. Finally, activate the complement.

One final step would consist in deactivating the warning about the file produced by OUTREG2. Open the Register Editor with Win+R and type regedit:

For Excel 365 / 2019 / 2016, use the following key:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security

Right click to create a new DWORD (32 bits key) named ExtensionHardening, double click and set the value to 0.

Finally, use Stata with a loop mixing local macro and global macro to estimate a baseline model and test its robustness:

**# Recessions - Full sample

global X "CAB RESGDP GDeficit GDebt CPI fuelX fuelM fo_r FI FM MIS_*"

local BAS_0 "$X"
local BAS_1 "$X IT"
local BAS_2 "$X cbie_index"
local BAS_3 "$X legal_n"
local BAS_4 "$X conflict_n"
local BAS_5 "$X MATR"
local BAS_6 "$X currencyEMP"
local BAS_7 "$X IT cbie_index legal_n conflict_n MATR currencyEMP"

	logit RGDP_p l1.(`BAS_0'), vce(cluster imfcode)
    estimate  store FULL_0
	margins,dydx(*) post
	outreg2 using mylogitfull.xls, replace ///
	dec(3) nonotes

forvalues V = 1(1)7 {
	logit RGDP_p l1.(`BAS_`V''), vce(cluster imfcode)
    estimate  store FULL_`V'
	margins,dydx(*) post
	outreg2 using mylogitfull.xls, append ///
	dec(3) nonotes
}

Comments and remarks are welcome, as always.