Excel with VBA-Excel

VBA stands for Visual Basic for Application. It is an event driven programming language used with MS-Excel, MS-Word.

Accessing the VBA editor

Go to File>> Option >> Customize Ribbon >>  Tick Developer >> Press OK

Click on Developer Ribbon and then click on Visual Basic.

 

Shortcut: Press ALT+F11

This is the VBA interface.

 

Writing the first Code ( Custom Formula)

In VBA code is written in Modules. So, Modules can be inserted by navigating to Insert >> Module.

Now in Module, Procedures are written. These are group of instructions (statements). There are two types of Procedure: 

a) Sub 

b) Function

These both eliminates the need of writing the same from multiple times. This allows to make custom user defined functions. Here is the example of a user defined function in excel.

So, the code snippet can be used as the Formula in the excel in similar manner as Formula are used in the excel. Now to use this as a formula in excel use the following statement:

=my_add(3,2)   

This will give the output based on the statement my_add = first + second and here the output will 3.

 

Linking code to a button

First add a button from Developer >> Insert >> Click on Button icon  then draw button in the sheet.

Now Assign the button with the code i.e, Macro. But before assigning the Macro Code should be written with the procedure name similar to Button1_Click()

To assign: Right Click on button >> Assign Macro >> Select Button1_Click()

 

Prompt a message box

The function MsgBox is used to prompt a message.

Syntax:

MsgBox(prompt,button,title,helpfile,context)

Prompt is the actual message that will be displayed the function execute. Except prompt rest of the parameters can be skipped.

Above is the code when a button is clicked then a message pop up will be displayed.

 

Prompt to input

The function InputBox is used to prompt to enter values in the field.

Syntax:

InputBox(prompt,title,default,xpos,ypos,helpfile,context)

Declaring Variable

Syntax:

Dim <variable_name> As <TYPE>

Variable name can be given based on rule to declare variable

TYPE: Integer, Double, Long, String, Date, etc.

 

Code Snippet to do some Excel task

Save a file

Syntax:

ActiveWorkbook.Save

Save As (macro enable excel file)

Syntax:

ActiveWorkbook.SaveAs Filename:=”filename.xlsm”,
FileFormat:=xlOpenXMLWorkbookMacroEnabled 

Save As / Export as PDF

Syntax:

Activesheet.ExportAsFixedFormat
Type:=xlTypePDF, Filename:=”filename.pdf”, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, OpenAfterPublish:=True

Select

Syntax:(To select a cell)

 Range(“A10”).Select        

Syntax: (To select a range)

 Range(“A10:C12”).Select

Enter Value in Cell

Syntax:

 Range(“A12”).Value = ”22”

Clear content of Cell/Range

Syntax:

 Range(“A12”).ClearContents

Cell/Range Font change

Syntax:

Range("F2:G2").Select
With Selection.Font
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.499984740745262
End With

Cell/Range Background change

Syntax:

Range("B13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

 

Bonus Tip:

There is no as such requirement to remember the above code snippets. This can be done by the help of Macros. Record the Macro by clicking the Start Recording under Developer Tab. A dialog will prompt to name the macro. Now, do the required task manually and then click on Stop recording at the same location. Successfully the Macro will be saved and go to the editor and the Procedure Code for that task will be there under Modules.