Tuesday, 2 October 2012

What is Excel VBA Option Explicit and Option Base

Option Explicit Vs. Option Base
Option Explicit

'The "Option Explicit" is a statement that must appear in a module before any procedures.

'When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements.

'If you attempt to use an undeclared variable name, an error occurs at compile time.

'If you don't use the Option Explicit statement, all undeclared variables are set to Variant type  by default ,unless otherwise you specifies it's type.

Example1:

Option Explicit

Sub CHECK1()

Dim y As Integer

x = 10

'Here an error occured Because it is an undeclared variable Type
'This Error will shown only when you use "Option Explicit" Statement
'If you don't use the "Option Explicit" Statement No error will shown and
'All the Undeclared Variables((here 'X') Types are Set to Variant Type by Default.

y = 20
Cells(1, 1) = x + y
End Sub

Example2:

Sub CHECK2()

Dim y As Integer

x = 10

'Here we haven't used the "Option Explicit" Statement so that No error will shown and
'All the Undeclared Variable(here 'X') Types are Set to Variant Type by Default.

y = 20
Cells(1, 1) = x + y
End Sub

Option Base:

Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. 
For eg. Option Base 1 will make the array lower bound as 1 instead of 0.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts