Working with variables in vba

As you know recording function in excel have limitation. Therefore you must learn Visual Basic programming language and the most importance part in programming is variables because we can do almost everything by applying these variables in programming. Without variables for sure your programming code will be very long and speed will be very slow. The definition of variables is a way of referring to a memory location used in a computer program. This memory location holds values- perhaps numbers or text or more complicated types of data like a payroll record.
In Operating Systems programs load into different parts of RAM so there is no way of knowing exactly which memory location will hold a particular variable before the program is run. By giving a variable a symbolic name like "employee_payroll_id" the compiler or interpreter can always work out where to store the variable in memory. Every variable has a name, called the variable name, and a data type. A variable's data type indicates what sort of value the variable represents, such as whether it is an integer, a floating-point number, or a character. The above definitions actually was taken from another website but it seem to complicated to understand. Don't worry about that because we will understand better when you practice more and more.
Basically we have two type of variables

1. Numeric (Numbers)
   
2. Non Numeric (Other than numbers)

We will explain in detail later in our next post.
Good Luck
Click here if you have any new project! Let us create for u for free.
Thanks

The best training tools for VBA

Actually the best training tools so far is record function in Excel. You can use this record function and playback again and again. After recording as you know you can view recorded code inside Visual Basic Editor Module. Therefore what you do is to record, view code and base on your recorded step understand these codes. If you kept trying and do trial and error of course one day you’re able to write your own code with simplicity compare with your recorded code because after recorded you’re are able to remove which code need or not. Now let try below code. Open your Visual Basic Editor and insert Module copy below code and paste inside.

Option Explicit
Sub Format1()

  ' Format1 Macro
  ' Macro recorded 8/9/2010 by mrvba.blogspot.com
  '
  Range("B2:D4").Select
  With Selection.Interior
   .ColorIndex = 6
   .Pattern = xlSolid
  End With
  ActiveWindow.SmallScroll Down:=18 ‘To scroll down
  Range("B20:D22").Select
  With Selection.Interior
   .ColorIndex = 3
   .Pattern = xlSolid
  End With
  ActiveWindow.SmallScroll Down:=-30 ‘To scroll up
  Range("A1").Select

End Sub

Actually the above code was recorded base on the step below:

1. Select Range (B2 and D4)
2. Fill this range with yellow colored
3. Scroll down
4. Select Range (B20 and D22)
5. Fill this range with red colored
6. Scroll up
7. Select Range A1

What do you think about your recorded code? Why we have so many scroll down, what happen if we delete? The answer if we delete nothing happen because scroll down is not compulsory step to completed the process. Therefore to simplify just remove scroll down code but end result remain the same. Good Luck Click here if you have any new project! Let us create for u for free. Thanks

Get to know Range and Cells property in vba

The smallest component in excel is cell follow by range, worksheet and workbook but the most importance is cells itself. If you notice in office 2003 excel column is limit to 256 and row limit to 65536 but more in office 2007. Meaning every sheets we only have 256 x 65536 cells. For column number started from 1 to 256 from left to right but by default A to IV unless you tick R1C1 reference style under Tools->Option->General Tab and for row number start from 1 to 65536 from top to bottom. Therefore before trying to write your own code please remember this otherwise you get stuck in your programming due to this limitation. The question now how to select this cells or range effectively using VBA? Actually there are many ways to apply depending on your program requirement. The methods you can apply as below:

1. Single cell
   • Range("A1").Select
   • Cells(1,1).Select
2. Range (Multiple cells)
   • Range("A1:B2").Select
   • Range(Cells(1,1),Cells(2,2)).Select
3. Rows
   • Rows("1:2").Select
   • Rows(2).Select
4. Columns
   • Columns("A:B").Select
   • Columns(2).Select

After select (Method if you still remember) then you can change this cells or range properties depending on your program requirement for examples border, fill color and etc. Click here if you have any new project! Let us create for u for free. Thanks

Working with Microsoft Excel Objects,Properties and Methods

The definition given by Microsoft for:

1. Object An object represents an element of Excel for examples workbooks, worksheets, range, cells and etc.Visual Basic actually object orientation programming language, nearly everything you do in Visual Basic involves modifying objects. There are four Excel objects you will work with more than any others: the Application object, the Workbook object, the Worksheet object, the Range object. The above sequences actually top to down object hierarchy.
2. Properties A property is an attribute of an object or an aspect of its behavior, for example when we refer to Range object it should be border, fill color and etc.
3. Methods A method is an action that an object can perform, for example when we refer to Range object action can perform should copy, cut and paste, delete and etc.

I think the above explanations is clear and easy to understand but don’t worry you will be able to understand when you continue try and error. The more you record the more you understand the coding. To be more clear understand refer below code and try by yourself.

Option Explicit
Sub SelectOjectAndChangeProperties ()

  Range("A1:A2").Select
  Selection.Interior.ColorIndex = 3

End sub

The above code will select (Method) Range A1 and A2 (Object) and change Interior color index (Property) to red. Copy the above into your modules and try it but don’t forget to enable your macro and set your security to medium. Click here if you have any new project! Let us create for u for free. Thanks

How to get started VBA code writing?

In previous lesson when we record macro the coding will be place in Visual Basic Editor (VBE) modules. Same goes to writing, if you want to write your own code go to VBE and insert modules and you can start writing begin with Sub and end with End Sub as below: Sub nameofyourmacro () Write your own code here! End Sub But before that we must learn Visual Basic programming language. In Visual Basic programming we will cover below topics:

1. Introduction to VBA
2. Objects and Collections
3. Workbook and Worksheet Object
4. Range Object and Cells Property
5. Methods and Property
6. Assigning Object Variables and Using Named Argument
7. Creating and Managing Array
8. Declare an Array With Dim Statement
9. Resize an Array With Redim Statement
10. Manage Dynamic Array
11. Create Multi-Dimensional Array
12. Find The Size of an Array
13. Working with Variables in Excel VBA
14. Message Box
15. Using If...Then...Else
16. For....Next Loop
17. Do.......Loop
18. Select Case........End Select
19. Font and Background Color

The above topic will be covered inside next tutorial. Actually you don't have to be worried because learning Visual Basic programming language it's easy unless you don't understand English. he eh. Good Luck.. Click here if you have any new project! Let us create for u for free. Thanks

Get to know your VBE (Visual Basic Editor)

VBE or Visual Basic Editor is an application attached to Microsoft Office like Excel, Word and Power point. This is where we kept our coding for each macro recorded. That why before we get started to write your own macro you must be able to use this application. Actually this application same as like any other office application, layout, button, function and etc except this application is design to suit the purpose as a programming tools. As a programming tools of course there must have form (interface design), module (code storage), debugging and etc. To open this as usual :

1. Open your new excel file
   
2. Under Tools menu Click on Macro then Macros
   
3. Click on Visual Basic Editor

Click here to learn how to open visual basic editor

Therefore you must learn how to use this application before get started and below items you must know first as well as others:

1. Project Explorer
   If you open blank excel and open your VBE at your left pane you will see Project – VBAProject, Under this you will be able to see folder list VBAProject(Book1) follow by Microsoft Excel Objects.
   
   
   Before recording no modules under project explorer.
   
   
2. Modules
   This is where your coding to be kept after recording or to write your on your own.
   
   
   After recording modules appeared.
   Note : Click on modules folder if you unable to see module1
   

Just play around with VBE then later you will get familiar because others function almost similar with others office application.
Good Luck.

Click here if you have any new project! Let us create for u for free.
Thanks