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.

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.

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.

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...

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 :)
  

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.

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.

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

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...!