introduction to vba: leveraging the power of ms excel
TRANSCRIPT
OSUINFORMS
Introduction to VBA: Leveraging the Power of MS Excel
OSUINFORMS 2015 Tutorial Series
Sayak Roychowdhury
PhD Student, ISE
1/17
OSUINFORMS
• Why use VBA?
• VBA Environment
• Declaration, Read, Write
• Data Types
• Fuctions
• GUI
• Record Macros • https://www.informs.org/Community/OSU-INFORMS
Outline
2/17
OSUINFORMS
• Comes with MS Excel, used almost everywhere
• Easy on syntax
• Read and Write data on excel sheets
• Can use built-in functions made for Excel, e.g. MAX,MIN, MINVERSE
Why use VBA
3/17
OSUINFORMS
VBA Environment
4/17
OSUINFORMS
VBA Environment
5/17
OSUINFORMS
VBA Environment
6/17
Project Pane
Property Pane
Editor
Run Buttons
OSUINFORMS
VBA Environment
7/17
WorkSheet Names
WorkSheet Properties
Module Names
OSUINFORMS
8/17
Declaration, Read, Write Begin Subroutine
Declare Variables
Read from WorkSheet
OSUINFORMS
9/17
Declaration, Read, Write Select/Clear
Write to Worksheet
Write to Worksheet (with loop)
OSUINFORMS
• Short,Integer, Long • Single, Double • Char • Date • Boolean • Object • Variant (can contain any type, except string)
https://msdn.microsoft.com/en-us/library/47zceaw7.aspx
10/17
Data Types
OSUINFORMS
11/17
Functions and Function Call
User-defined Function
Function Call
Excel Function Call (Generate normal random number)
OSUINFORMS
12/17
GUI Developer Tab
Assign Macro to command button
OSUINFORMS
• Developer Tab -> Click Record Macro ->Give Name of the Macro-> Do the task-> Stop Recording
• Macro is saved in the module
13/17
Record Macro
OSUINFORMS
Hope it was useful! 14/17