VBA Statement - Dim with Intellisense menu

To write Dim Statements:

To make your VBA coding more easy after typing Dim VarName As follow by space-bar the Intellisense drop-down menu will appeared as below picture. Continue typing this Intellisense menu is giving more narrow suggestion of Data type for you to select. Use mouse or arrow down key to select and press Space-bar or Tab button or Mouse Double click to confirm.

If the Intellisense drop-down menu not appeared just press Ctrl + Space-Bar Button Or Ctrl + J button.

To Access Variables in Project:

Same as above just click empty area below your variables declaration just press Ctrl + Space-Bar Button Or Ctrl + J button. Continue typing and from drop down menu use arrow down key or mouse to select and press Space-bar or Tab button or Mouse Double click to confirm. Refer below picture for details.

Note:
By using this method we reduce typo error in our coding. This Intellisense drop down menu also appear after "." (Dot).

Read more about Dim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Dim-statement
Other Reference-Dim-statement

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

How to open Visual Basic Editor under Microsoft Excel?

Visual Basic Editor (VBE) is the place to write and store all Visual Basic code and Macro recorded code. This VBE attached to all office application for example Microsoft Word, Microsoft PowerPoint and etc. but in this blog our focus only VBE in Microsoft Excel. To open this VBE:

Two ways to open Visual Basic Editor in Excel:

1. 1st Method: Under Developer tab click at Visual Basic.
   
   
   
   
   
   
   
   
   
   
   
   Click here if Developer tab is not visible.
   
   
2. 2nd Method: Short cut key by Pressing Alt + F11 button.
   
   
3. Visual Basic Editor will open as below:
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

 

Practice makes perfect. Thank You.

Get started with all Function and Formula in Excel

To be a good programmer in Microsoft Excel we should understand every function in excel itself. To open all these function:

1. Click Home tab.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Home tab Click at Arrow down button beside sum symbol.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at More Functions.
   
   
4. Select a category.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Select a function and this function or formula will be inserted into selected cell.
   

All this functions already build in and can be use instantly or call using VBA code. To create new function we must create using VBA code inside Excel workbook and this will appeared under this insert function dialog box.

The most importance function is under Math & Trig which is related to number  and Text which is related to string.

Get started with recording macros to learn VBA code

Better way to learn VBA code is to record every move or action and check the recorded code. From here we can learn and modify the code to suit your need. 

To record macro:

1. Under Developer tab Click at Record Macro.
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Record Macro give your Macro name.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Default name given is Macro1,2,3..... but we can rename to differentiate the function.
   Macro name must begin with letter, maximum character 255 and must not contains space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
   Detail we refer here:
   Microsoft Reference - Macro naming rules
   
   Note: Under this dialog box you can choose to store recorded macro under This workbook, New Workbook or Personal Macro Workbook and also create short cut key to run this macro later.
   
   
3. Click OK to begin recording and continue your work in Excel as usual.
   
   
4. Upon completed click at Stop Recording.
   
   
   
   
    
   
   
   
   
   
   
   
5. Save this file under Excel Macro-Enabled Workbook(*.xlsm).
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

 

 To playback recorded macro:

1. Under Developer tab Click at Macros.
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Macro select Macro name: and click Run.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Note: Under this dialog box you can choose macros in This Workbook or All Open Workbook.
   
   
3. All the step recorded before run automatically.

To check recorded code:

1. Under Developer tab Click at Macros.
   
   
   
   
   
   
   
   
   
   
2. Under Macro select Macro name: and click Edit.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Visual Basic Editor will open and show code.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   The code will be under module, try to understand and edit the code and run again and again.
   

Practice makes perfect. Thank You.

How to set security to enable macros in Microsoft Excel?

By default any Macro or VBA code inside or Excel file will be disable for security reason. Therefore we must understand the risk but for me as long we know the source then should be OK.

Follow this step to change or set the security to enable macros:

METHOD 1:

1. Open any Excel file and click at File top left.
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Click at Options below left.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at Trust Center under Excel Option.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
4. Click at Trust Center Setting under Excel Option.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Click at Macro Settings under Trust Center.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Choose either one but since we know the source code prefer to choose no 4 which is
   "Enable all macros (not recommended; potentially dangerous code can run)" to ease our VBA code writing later on.
   
   
6.  Click OK.
   

          OR

METHOD 2:

1. Click at Developer tab on top follow by Macro Security.
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Repeat Step 5 and 6 in Method 1.
   

Macro security setting is completed.

Microsoft Reference

How to show Developer tab in Microsoft Excel?

Before started your 1st code in VBA we need to show Developer tab in order to use all function for example Visual Basic Editor, Macros, Record Macro, Macro Security, Add-ins, Control and XML as below:

 

By default is hidden we have to show this tab by using below step:

1. Open any Excel file and click at File top left.
   
   
   
   
   
   
   
   
   
   
   
2. Click at Options below left.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at Customize Ribbon, Under this check Developer check box.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
4. Click OK. 
   

The Developer tab automatically show. Done.

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

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

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.

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