Sunday, September 12, 2010

Working with variables

To have better understanding in variables lets take look below example:
Sample without Variable:
Option Explicit
Sub example1()
  MsgBox(“Hisyam”)
End sub
Sample with Variable:
Option Explicit
Dim myName as String
  myName = “Hisyam”
  MsgBox(myName)
End sub

Open your Excel then VBE and insert module, copy paste the above code into this module and try to run both example1 and 2. There is no different in between because end result is the same. The different example1 we use constants and example2 we use variable to declare our name with myName. If you notice when we use constants we need to use quote before and after but not for variables. Use must remember this rule before get started because variables is more effective in repeating process like looping and for next. 

Constant = MsgBox(“Hisyam”) with quote before and after. 

Variable = MsgBox(myName) without quote.

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

Thanks Good luck

Friday, September 3, 2010

How to declare variables in VBA?

Basically same with Visual Basic when declaring variables, you usually use a Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable. Or it may be placed at the top of a module, in the Declarations section, to create a module-level variable. Another method is to use Public statement to make this variable available to all procedures in the project. For Examples: Dim x as Integer (Commonly use) Public x as Integer x is our variables and the data range is in between -32,768 to 32,767 (Integer) which is reserve by computer to store x. Meaning if your data is more or less when you run this macro your computer either ignore or error message to trigger value is out of range. To have clearer picture lets try below example:
Option Explicit
Sub CalculateAndB()

  Dim ColA as Integer
  Dim ColB as Integer
  Dim Result as Integer
  ColA = Range(“A1”).Value
  ColB = Range(“B1”).Value
  Result = ColA*ColB ‘Multiply value of Range A1 and B1
  Msgbox (Result)

End Sub
Now copy the above code into your module and try. 1. Input 8 into Range A1 and 100 into Range B1 and Run the macro Your message box will prompt 800 (No problem because still within integer range) 2. Input 80 into Range A1 and 1000 into Range B1 and Run the macro You will receive error message “Run time error 6 Overflow”. This is because after multiply result will be 80,000 (Out of integer range) From the above example you should declare “Result” as Long instead of integer because Long can accept data range in between -2,147,483,648 to 2,147,483,648 Repeat the above testing but this time changed Dim Result as Integer ---à Dim Result as Long Sometimes we tend to forgot to declare if we have too many variables. As a reminder you can use “Option Explicit” statement above Sub or 1st line in a module. This will force you declare. Click here if you have any new project! Let us create for u for free. Thanks

Thursday, September 2, 2010

What and Why we use variables in programming?

The opposite of a variable is a constant. Constants are values that never change. Because of their inflexibility, constants are used less often than variables in programming. Therefore when we need data which always change we need to use variables for example this expression X + Y. X and Y are variables. Normally we use variables when we know the data range only for example user input data, calculation and etc.
Below Table is to show what data type to declare and what range it will be. 1st Table is for Numeric (Numbers) and 2nd Table is for Non Numeric (Other than numbers).

Numeric Data Types


Non numeric Data Types


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. Variable name is not limit to X and Y only but you can use any name up to 255 characters if I’m not mistaken.

If you go to help in VBE the definition of variable as below:

Variable is a named storage location that can contain data that can be modified during program execution. Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.
Variable names must begin with an alphabetic character, must be unique within the same scope, can't be longer than 255 characters, and can't contain an embedded period or type-declaration character.
Actually you can use variable without specified data type but you have disadvantages:
  1. If you have very long code then it will be very difficult for you to do the editing because you do not know which variables belong to which function.
  2. Your program will be slower because it will consume your memory. If I’m not mistaken data type will be assume as variant (Highest range).
  3. Affect your program accuracy because sometime you only need only numbers but end result may be not numbers.
Therefore please consider to declare your data type for variables when you start writing.
Good Luck
Click here if you have any new project! Let us create for u for free.
Thanks

Tuesday, August 10, 2010

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

Monday, August 9, 2010

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

Friday, August 6, 2010

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

Thursday, August 5, 2010

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

Thursday, July 29, 2010

How to learn VBA in easy way?

Base on my experience learning VBA is really fun because after created you can try instantly to check whether your coding is correct or not. By doing this we can learn from try and error because no harm if you repeat again and again. Now here is the step you can follow to learn VBA:
  1. Record function
    Record your macro and try to understand the coding
  2. Try and Error
    Edit the recorded code and give another try and see the result
  3. VBE help
    This is another source of tutorial because when we search certain keyword there is a lot of example that you can follow
  4. Internet (The best teacher in the world)
    There are many website offer free tutorial online. Clear example this blog… he he
  5. Group or Forum
    Joint Google group or forum for this topic for sure you found many tips and advise
  6. Of Course Books or e-Books (Suggested later if you wish..)
The above step was from my experience if you have better idea give your comments.
Good Luck.
Click here if you have any new project! Let us create for u for free.
Thanks

Macro recording limitation

When you use recording to create macro actually too many limitations because of flexibility for examples:
  1. Take longer step to completed
  2. Coding too long difficult for editing
  3. Unable to loop through workbooks, worksheets, rows and columns
  4. Unable to retrieve outside data
  5. Unable to call function or other sub
  6. Unable to do calculation and
  7. etc
Due to this limitation we must learn how writes visual basic programming language to create more advances macro application. This programming language actually not so difficult to learned if you understand English. Now you understand why we need to master visual basic programming language to be master of VBA.
Good Luck
Click here if you have any new project! Let us create for u for free.

How to record macro effectively?

Base on my experience macro recording process is simple but the most problematic is playback. Some time after playback too many errors occurred because of similarity in between workbook, worksheet or cell is not there and some time it was due to data distribution. Therefore before any recording being done you must plan ahead what is your future workbook look like for you to playback for example workbook name, sheets name and etc. Below tips is for you remember how to record macro effectively:
  1. Always begin one step behind.
    Example: If you wish to select sheet1 for every playback then before recording start you must select other sheets, same goes to range.
  2. If you use formula inside your macro then it is better to remove this formula by copy and paste value
  3. If you wish to fill certain range with formula or data use short cut key Ctrl(hold) + Enter(Followed)
  4. To select certain range in column always use short cut key Ctrl(Hold) + Shift(Hold) + arrow right(Followed)
  5. To select certain range in row always use short cut key Ctrl(Hold) + Shift(Hold) + arrow down(Followed)
  6. To select certain range for all always use short cut key Ctrl(Hold) + Shift(Hold) + End(Followed)
  7. Start and End with selecting Range A1
  8. Minimize the usage of scroll it will occupied your code for nothing
  9. Standardize sheets name
  10. Plan your recording step
Hopefully the above guide will help to minimize the playback error.
Good Luck
Click here if you have any new project! Let us create for u for free.

Tuesday, July 27, 2010

How to change button face for the macro?

If you still remember we can assigned any macro to the custom button but we only have smiley face. Actually this button is not limited to smiley face because we can customize by changing appearance included text. To do this we must get back to tools menu and select customize, during customize dialog box still open select this smiley button. There are 2 ways to change
  1. Right click at smiley face
  2. Click at Modify selection button

From the above figure you can see command to change button image and if you select another set of image will appeared. Just select this image and you're done.
If you choose command edit button image then button editor dialog box will appeared as below.



Now you can start creating your own image by changing the pixel inside picture box and click OK when you're done.
Good Luck
Click here if you have any new project! Let us create for u for free.

Monday, July 26, 2010

How to use macro from another workbook?

When you record a macro then save, it doesn't mean this macro can only be use for this workbook. This macro actually can be use for all open workbooks as long the original macro workbook still open. For example 1st workbook open contain macro and enable, after that open another workbook without macro inside. You still can run macro from new workbook by pointing to Tools menu; macro and macros then select your desired macro with condition new workbook open from the same root. For example your macro workbook opens from desktop with double click but new workbook from start menu – excel open then your macro will not take place.

From the above figure you can see if you run macro from another workbook file name appeared first and followed by macro name. As usual select this macro, then click run. You're done!

Good luck.


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

How to create short cut key for the Macro?

Actually the easier way to open VBE is from run macro. After you click “Run”, macro dialog box will appeared. At your right hand there are many buttons for you to act. 1st button of course Run follow by Cancel, Step into, Edit, Delete and Option. If you wish edit select your macro then click edit button and now you’re able to see the code behind. Same goes to Step into button but more on debugging mode when you enter you need to press F8 to see step by step process(highlighted with yellow) until finished then press stop or reset button to exit.



Sometime you feel easier to have short key for each individual macro created or recorded for you to run. To do this you must click at Option button after you select desired macro, macro option dialog box will appeared then you will see macro name, short cut key Ctrl+ “key in yours” and description given and you’re done. Next time if you wish to run this macro just press Ctrl + “any key enter before” and this macro will run automatically.

Thursday, July 22, 2010

Do you have any VBA project for Me?

Today I have completed another vba project started last 3 months. This project is about making operation standard (something like manual) to insert electrical components by hand into PWB (printed wire board). I have given 5 things
  1. Bill of material with part no. location and quantity (Average about 60 components)
  2. Photo library for components
  3. Coordinate X and Y for location
  4. All standard precaution
  5. Standard format
Base on the above items I must create operation standard in excel. Total I have about 10 module because to many process need to complete at one time. The process included
  1. Comparing multiple sheets of Bill of material
  2. Call components photo and place exact location
  3. Create balloon for numbering
  4. Transferring data from Bill of material into standard format
  5. Create text box for location
  6. Converting X and Y data into rows and column
  7. Create arrow pointing exact location on PWB
  8. Call for standard precaution
  9. Etc
Basically this macro can save preparing time from 2 days into 2 hours. As you can see how effective this macro is? Therefore If you have any project please send to me because I'm willing to help for free. E-mail me at geniusideas[a]gmail.com but don't forget to replace [a] with @ because I worried spam robot detected my e-mail or leave your request in comment column. Thanks

How open visual basic editor (VBE) in Excel?

After recording and playback as many as you like for sure you wanted to know what code behind so that you do the editing and write your own code to suit your needs. In order to do that we must know how open visual basic editor (VBE) from excel. There are many ways to do that:
  1. Use short cut key by pressing Alt + F11
  2. Point to Macro on the Tools menu and select Visual Basic Editor



  3. Click Visual Basic Editor Icon at Toolbar (As below image)



Now the editor is open.



Normally you won't be able to see the code immediately because still hiding under Modules folder. Now double click this folder you will see module1 or just click at plus sign beside this folder. Click this module1 and now you will see the code as below:



The above code example is from our previous macro recorded. The process as below:
  • Select Range A1
  • Fill with Red colored
  • Select Range A2
  • Fill with Green colored
  • Select Range A3
  • Fill with Blue colored
  • Select Range A1 again
Basically VBA code start with Sub (Open), macro name, some note(Start with hyphen(')), active code and End sub (Close).
Now you can start the editing. Good luck
Remember to follow the fire...

Tuesday, July 20, 2010

How attached macro into button?

Sometime you feel very difficult to run macro from toolbars because too many step and macro inside. Therefore what you can do is to create button and attached macro to it. There are a few methods you can do:
  • Standard Text Box
  • Custom button
For no 1) Standard Text Box the step you follow as below:
  1. Use your creativity to edit this text box look like actual button.
  2. Move your cursor at the edge of text box and right click.

  3. Assign macro dialog box will open then choose macro and click OK.

You’re done for no 1), every time you click this fake button your macro will start working.

For no 2) Custom buttons the step is more complicated:
  1. Point to Customize on the Tools menu.



  2. Inside Customize dialog box choose Commands tab and select macro.



  3. Click and drag smiley button anywhere in between any button you like (as below) – No limit to visual basic toolbar only



  4. Close customize dialog box and click button after drag(smiley face) then assign macro dialog box will open then choose macro and click OK.(Same as text box item 31c)
You’re done.
Note:
  • For this type of button it will permanently attach to any excel file as long the original file still inside your computer.
  • The outlook for this button also can be change or customize. (May next tutorial… he he :)

Monday, July 19, 2010

Why my recorded macro doesn't work?

In order for you to run the macro, 1st thing you must do is to set security setting medium otherwise your macro totally useless. To do that you must follow the step below:
  1. Point to Macro on the Tools menu, and then click Security..



  2. In Security dialog box tick Medium

This can be done to any excel file. Just open blank excel file follow the step. You're done.
By doing this when you open any excel file that contain macros, Excel will ask whether you need to disable, enable or More Info as below



If you wish to use the macro just click enable then the macro remain active.
Just follow the fire...Good Luck with your macro.

Sunday, July 18, 2010

How to record and playback macro from Toolbar?

Actually there is another alternative to record and playback the macro which is from toolbars (Visual Basic) as below:



For recording just press Record Macro button (circle) then record macro dialog box will appeared.



To playback just press Run Macro button (triangle) then playback macro dialog box will appeared.
If the above toolbars is not visible then you should follow below steps:
Go to view at menu bar then click Toolbars and tick visual basic.



Another way to view this toolbar is right click at empty area beside menu bar then tick visual basic.
Now you can start playing with recording and playback the macro.
Have Fun....
Tips:
The best tools to learn is by trial and error. Use your imagination and written down your step, minimize number of step to improve your macro speed and accuracy.

Saturday, July 17, 2010

Get started with VBA. How to playback recorded macro?

In previous lesson I teach how to use record function in Excel and now we learn how to playback recorded macro. If you have nothing to record then follow example below.
Example:
Open new excel file. Now go to Tools, Macro then Record new macros.. Give this macro name as format1. Range A1 fill with Red, range A2 fill with Blue and range A3 fill with Yellow You should get the result as below.



Don't forget to press stop after the above process.
Now playback time...
The step to follow as below:
Select different sheet from the above macro recorded.
  1. Point to Macro on the Tools menu, and then click Macros.



  2. In the Macro dialog box, select macro under macro name:



  3. Click "Run"
You're done. The result should be the same as above if you follow the above example.
Now should you should know:
How to record and playback recorded macro... Have fun with macro

Wednesday, July 14, 2010

Get started with VBA. How to use record function in excel?

The purpose of this blog is to share and may be can help you learn VBA programming with Excel. No prior programming experience is required or expected like myself. Let's talk about myself, at the beginning of learning process the key for success is interest and may be a bit pressure because of the difficulties in excel application if you do something again and again after quite sometime. Therefore from here I started to look for something in advance how to do it? I asked my friend! the answer "Why not you use macro function in Excel?". Now I realize there is a function call macro that you can start and stop recording any process you have done and repeat again and again. Then later he show me what code behind after recording in VBE (Visual Basic editor) which is attached to any office application.
Here's how to record a macros:
  1. Point to Macro on the Tools menu, and then click Record New Macro.



  2. In the Record Macro dialog box, type a name for the macro in the Macro name box. Macro names must start with a letter and can include letters, numbers, and underscore characters, but can't include spaces. By default macro name started with Macro1,2,3,.... in sequence. You don't need to change the other boxes:



    Record Macro dialog box

    When you click OK, the Stop Recording toolbar appears, and you're ready to record. Until you stop the recording, every Excel command and keystroke will be recorded in the macro, in the order in which they are entered.
  3. Start any process you like.
  4. To finish recording the macro, click the Stop Recording button
You're done.

What is VBA? Why Excel application?

Actually VBA stand for Visual Basic for Applications which is commonly used to work with Microsoft’s Office applications (Word, Excel, Access, and PowerPoint). If you wish to complete your daily task automatically in Microsoft word or excel then you should master this built-in programming language. You need not write complicated programs using VBA in order for it to be useful to you. At the very least, knowing VBA will make it easier for you to analyze relatively complex problems for yourself included calculating, formatting, collecting, managing and etc. VBA is relatively easy to learn, the most easy way is by recording. After recording you must be able to read and understand the code behind each process recorded. Now set our focus on Excel application because of usefulness and easily understand due to more object contain for examples Workbook, Worksheet, and Range. The property of ranges and cell references, formulas, built-in functions, and charts in excel make our VBA work well because we can include mathematical ways to let our program running through out column by column, row by row, sheet by sheet included workbook. So far VBA help me a lot because my job become easier, faster, accurate and of course productivity up. What VBA can do for you?:
  1. Create summary sheet from multiple sheets.
  2. Create summary sheet after collecting data from server include image.
  3. Create summary sheet after reading all files in multiple folder.
  4. Create summary sheet after comparing multiple sheets or workbooks.
  5. Formatting when file open
  6. Calculating
  7. Chart plotting
Of course many more you can do! what ever you think for sure can...Have fun to learn VBA...!