What is the syntax to declare a worksheet object?

Dim <object name> As Worksheet
Set <object name> = Worksheets("<sheet name>")
'example of simplification with the worksheet object

'Original code
Worksheets("exampleSheet").Range("A1") = 6
Worksheets("exampleSheet").Range("B2:B9").Font.Italic = True
Worksheets("exampleSheet").Range("B2:B9").Interior.Color = rgbRed

'Simplified code
Dim sht As Worksheet
Set sht = Worksheets("exampleSheet")

sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed

 

 

Advertisements

What is the syntax to declare procedures?

Private Function <your name> ()
   ‘your code here
   ‘callable only by procedures in the same module
End Function


Public Function <your name> ()
   ‘callable from any procedure
End Function

 
Private Sub <your name> ()
   ‘callable only by procedures in the same module
   ‘cannot be called from Macros dialog box
End Sub

 
Public Sub <your name> ()
   ‘callable from any procedure of any module
   ‘can run procedure from Macros dialog box
End Sub

What is the syntax to declare variables?

'to declare procedure variables
Dim <variable name> As <variable type>

‘to declare procedure variables that retains value
Static <variable name> As <variable type>

‘module variable
Private <variable name> As <variable type> 

‘global variable
Public <variable name> As <variable type> 

 

What is the syntax to declare an array?

Dim <array name>() ‘array without size
Dim <array name>(<integer>) ‘array with size
Dim <array name>(<integer>) As <data type> ‘declares array with size and data type
‘examples
Dim myArray(3,4) As Integer ‘a 2D array
myArray(2,1)=0

Dim myArrayTwo()
myArrayTwo=Array(1,2,3,4,5)