[ xls ] VB Tips about VBA and macros in excel

1. From the Sheet, Right Click > Show Codes

2. The VB editor will be appears

TIPs for VB editor

– Go to definition ( Open Declaration )

right click > definition

Go back

right click > last position

– To prevent the screen swaping

Application.ScreenUpdating = False

or if we want to set it back

Application.ScreenUpdating = True

– Activate Worksheet

Worksheets(strSheetName).Activate

– Access Cell Values ( For example, if we want to access E4 cell

Worksheets(strSheetName).Range("E4").Value

– MsgBox function

definition

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

prompt : string

buttons : can be vb MsgBox constanti.e. vbCritical

title : string

– Go To

Declaration

XXX: 

Usage

On Error Go To XXX

OR

XXX

– recordset

If Not rs Is Nothing Then rs.close

OR

rs.EOF

TIPs : record macro

1. View > Macros > Record Macro

2. do the event in excel ( i.e. save file as CSV )

3. View > Macros > Stop record Macro

The excel will generate the code for that action. The result will be in VBA modules.

TIPs : excel change column width and height

Home > Cells > Format

Insert Button

For MS 2010

Developer > Insert > ActiveX control > Button

Writing Excel macros

– save as .xls ( .xlsx does not contains all macro )

Option Explicit : force define variable ( This will be error at compile time. In VBA, we can check by `Debug > Compile VBA Project`.

On load worksheet and On Terminate Sheet

Note that the code is `Worksheet`, do not replace it with worksheet name !!!!

Private Sub Worksheet_Activate()
MsgBox ("Hi")
End Sub

Private Sub Worksheet_Deactivate()
MsgBox ("Bye")
End Sub

Protect VBA code

In VBA screen, Tools > VBAProject Properties

Protection tab
Tick Lock project for viewing
and set password