#2: Setting up the header variables

Feb 4, 2021

Clinical Research Data 101

Tools/Languages: Excel VBA Macro

This tutorial shows you how to set up the same header variables for every spreadsheet.

If you need to create the same spreadsheet with the same header row.

First of all, here's the link to learn how to turn on the "Developer Mode" in your Excel.

In clinical research, we often need to create a simple report with the same header variables.

What happened?

With these few simple lines of codes, you will be able to enter the same header variables with a single click. You can change the header variables to your own needs.

What should I do now?

• Open the Excel spreadsheet and turn on the "Developer" mode as described at the beginning.
• Click "Visual Basic" on the top ribbon (first one on the left).
• Insert a new "Module" and copy/paste the codes below. You can add more header variables by changing the Range("A1").Select to the new cell locations.

  
  Sub header()
  
  
  Range("A1").Select
  
  ActiveCell.FormulaR1C1 = "study_id"
  
  Range("B1").Select
  
  ActiveCell.FormulaR1C1 = "first_name"
  
  Range("C1").Select
  
  ActiveCell.FormulaR1C1 = "last_name"
  
  Range("D1").Select
  
  ActiveCell.FormulaR1C1 = "site_id"
  
  Range("E1").Select
  
  ActiveCell.FormulaR1C1 = "email"
  
  Range("F1").Select
  
  ActiveCell.FormulaR1C1 = "phone"
  
  End Sub
  						

• Go back to the spreadsheet and click the "Macros" icon on the top ribbon. Then assign the macro to "header" and click "OK".

Please leave me a message if it doesn't work for you.