VBA - IT and app PDF

Preview

Summary

IT and app...

Description

What is VBA? VBA stands for Visual Basic for Applications; it is an event driven programming language developed by Microsoft. It is predominantly used with Microsoft office applications like MS-word, MS-Access, and MS-Excel. The process of telling the computer what you want it to do for you is what is known as computer programming. Just as you used English to tell the maid what to do, you can also use English like statements to tell the computer what to do. The English like statements fall in the category of high-level languages. VBA is a high-level language that you can use to bend excel to your a11 powerful will. VBA is actually a sub set of Visual Basic 6.0 BASIC stands for Beginners AllPurpose Symbolic Instruction Code. For business use, you can create complete powerful programs powered by excel and VBA. The advantage of this approach is you can leverage the powerful features of excel in your own custom programs.

Why VBA? • • • •

It uses English like statements to write instructions Creating the user interface is like using a paint program. You just have to drag, drop and align the graphical user interface controls. Short learning curve. From day one that you start learning, you can immediately start writing simple programs. Enhances the functionality of excel by allowing you to make excel behave the way you want it to.

Visual Basic for Applications (VBA) basics Before we can write any code, we need to know the basics first. The following basics will help you get started. •

Variable – in high school we learnt about algebra. Find (x + 2y) where x = 1 and y = 3. In this expression, x and y are variables. They can be assigned any numbers i.e. 1 and 3 respective as in this example. They can also be changed to say 4 and

•

2 respectively. Variables in short are memory locations. As you work with VBA, you will be required to declare variables too just like in algebra classes. Rules for creating variables o Don't use reserved words – if you work as a student, you cannot use the title lecturer or principal. These titles are reserved for the lecturers and the school authority. Reserved words are those words that have special meaning in VBA and as such, you cannot use them as variable names. o Variable names cannot contain spaces – you cannot define a variable named first number. You can use first Number or first number. o Use descriptive names – it's very tempting to name a variable after yourself but avoid this. Use descriptive names i.e. quantity, price, subtotal etc. this will make your VBA code easy to read • Arithmetic operators - The rules of Brackets of Division Multiplication Addition and Subtraction (BODMAS) apply so remember to apply them when working with expressions that use multiple different arithmetic operators. Just like in excel, you can use o + for addition o - for subtraction o * for multiplication o / for division. • Logical operators - The concept of logical operators covered in the earlier tutorials also apply when working with VBA. These include o If statements o OR o NOT o AND o TRUE o FALSE

Under Developer tab from the main menu, click on "Visual Basic" icon it will open your VBA editor.

VBA Excel Form Control & ActiveX Control

Creating VBA Form and GUI controls in Excel GUI is the acronym for Graphical User Interface. The GUI is the part of the program that the user interacts with. A GUI is made up of controls. These controls can be used in a Form. The following table shows some of the most commonly used GUI controls in VBA. S/N Control 1. Command Button 2 Combo Box 3. Check Box 4. 5. 6. 7.

List Box Text Box Scroll Bar Spin Button

8.

Option Button

9. 10. 11.

Label Image Toggle Button

Description Used to execute code Used to present a dropdown list to the users Used for true or false values. Multiple check boxes can have true value at the same time. Used to present a sample list to the users Used to accept user input Used for to provide a scrollbar for navigation purposes Used to increment or decrease numeric values by clicking on up or down arrows that appear in the spin button. Used for true or false values. Only one option button can have a true value at a time. Used to display static text to the user Used to display images Button that has pressed an un-pressed states

1) Mention where you can write your VBA program for Macro? Module is the place where you can write VBA program for Macro, to insert a Module navigate to Insert ->Module 2) Mention what are the comments style used in VBA? Comments are used to document the program logic and the user information with which other programmers can work seamlessly on the same code in future. There are mainly two methods in VBA to represent comments. • •

Any statement that begins with a single quote is treated as comment Or you can use statement REM instead of single quotation mark (')

3) In VBA how we can declare variable and constant? In VBA, variable can be declared with the keyword “DIM” while constant is declared with keyword “Const.” 4) What are the data types in VBA? Data types in VBA are classified into two categories Numeric Data type

Non-numeric Data type

Byte

String (fixed length)

Integer

String (variable length)

Long

Date

Single

Boolean

Double

Object

Currency

Variant (numeric)

Decimal

Variant (text)

5) Explain what is the meaning of “Option Explicit”? Where it should be used? “Option Explicit” makes the declaration of variables mandatory. Line explicit function makes the compiler to determine all the variables that are not declared by the dim statement. This command diminishes the problem of type errors significantly. It is used in VBA because it deals with information rich application in which type errors are common. Before starting any, sub-procedures it can be used inside a module. 6) Explain how can you pass arguments to VBA functions? When arguments are passed to VBA functions, they can be passed in two ways •

ByVal: When argument is passed by Value, then it means that only value is passed to the procedure, and any changes that are made to the argument inside the procedure will be lost when the procedure is exited

•

ByRef: When the argument is passed by reference than the actual address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be recalled when the procedure is exited

7) What is the code to find a last used Row in a column or last used Column of a row? To find the last row in a column, the command used is End(xlUp) and to find last column in a row, the command used is End(xlToLeft). 8) Mention the difference between the Subroutines and Functions in VBA? The difference between the Subroutines and Functions is that • •

Subroutines never return a value, but function does return values Subroutines could change the values of the actual arguments while a function could not change the value of actual arguments

9) Explain what is the difference between CurrentRegion properties and UsedRange? •

CurrentRegion: The current region is a range bounded by any combination of blank columns and rows

•

UsedRange: This property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specific worksheet

10) Explain how to debug a VBA code? To debug a VBA code, follow the steps • • •

Using Breakpoints (F9) Step by step execution (F8) Print & Immediate Window and Watch the window

11) How can you stop VBA script when it goes into the infinite loop? By pressing Cntrl+ Pause Break key one can stop VBA script when it goes into infinite loop....