Run VBA in R

Live Online Training : Data Science with R

- Explain Advanced Algorithms in Simple English
- Live Projects
- Case Studies
- Job Placement Assistance
- Get 10% off till Sept 25, 2017
- Batch starts from October 8, 2017

This tutorial describes how to run Visual Basic (VBA) script in R.
VBA in R
Most of the times, we need to integrate R with Excel to make formating changes in excel workbook. With VBA, we can do a lot of things such as creating pivot table, applying functions, reshaping data, creating charts etc. It would help to automate the whole process. For example, building and validating a predictive model in R and exporting predictive probability scores to excel workbook. In R, call vb script to open the exported excel file and prepare gain and lift charts in excel.

Step 1 : Write VB Script and Save it as .vbs file

Sample Visual Basic Script

The following program tells Excel to open the workbook and apply borders to the used range in the sheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts=False
Set wb = objExcel.Workbooks.Open("C:\Users\Deepanshu\Documents\example.xlsx")
Set Xlsheet = wb.Worksheets("PRDSALE")
Xlsheet.UsedRange.Borders.LineStyle = xlContinuous
Xlsheet.UsedRange.Borders.Color = RGB(0, 0, 0)
Xlsheet.UsedRange.Borders.Weight = xlThick
wb.save
Paste the above script in Notepad and save it as .vbs file
For example, give a name to the file as border.vbs and select 'All Files' from 'Save as type:' (see the image below).
VBS File

Step 2 : Run the following code in R
pathofvbscript = "C:\\Users\\Deepanshu\\Documents\\border.vbs"
shell(shQuote(normalizePath(pathofvbscript)), "cscript", flag = "//nologo")
pathofvbscript : It is the path where visual basic script is stored. The shell function calls a System Command, using a Shell.

VB Script : Run Excel Macro from R

The following program tells excel to open the workbook wherein macro is stored and then run it.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts=False
Set wb = objExcel.Workbooks.Open("C:\Users\Deepanshu\Documents\Book1.xls")
objExcel.Application.Run "Book1.xls!macro1"
wb.save

R Tutorials : 75 Free R Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Run VBA in R"

Post a Comment

Next → ← Prev