Easy VBA Macro (No Code Required)

Easy VBA Macro (No Code Required)

For Beginners...

 

 

In previous few articles we have discussed about how to create Macros in Excel VBA and applied some coding. In this article we'll make VBA more easy.

It is very interesting and surprising to know that you can create your own Macro without writing a single code and can Automate your day to day work by following few easy steps. As Excel is very user friendly tool so it gives two ways to create Macro. One is the Traditional way where you can write script in VBA and another is Smart way where you can create Macro by recording steps.

By recoding Macros you can automate your day to day repeated and time consuming work which you perform in excel and for this there is no requirement to be an excel expert.

Here are the steps:

• Go to Developer menu

• Click on Record Macro button as mentioned below

• Now Name your Macro and give a short cut key (optional) which trigger your Macro and select the Location where you want to store the Macro (Default Location: This Workbook)

• If you select New Workbook then a new Workbook get opens and all the recording will save there.
• Click OK
• Perform the task which you want to record or Automate
• Click on Stop Recording

• Now your Macro has been Recorded/Created
• You can execute your Macro by pressing Short key given to Macro

• Now you can also view the coding behind the macro. To review the coding press Alt+ F11

Tip: You can Start and Stop recording Macro by clicking record Macro icon below Sheet Tabs

Note: Recording Macro Coding will not be found in any module as recording did not create module.


We will talk more about excel & VBA in the next article.

You can share your feedback and post your excel queries in this blog.

Message Box (Excel VBA)

Message Box (Excel VBA) For Beginners

In Previous Article "My First VBA Code (Macro)" we have learnt, how to create Message Box or How to write first VBA Codes which gives a brief idea to about how to start writing Macro/VBA Codes in Visual Basic for Applications.

This article help the user to learn details of Message Box which can play an important role in a program.
 
Message Box is a dialog box containing messages/information for users to respond, it returns with an Integer value indicating which button the user has clicked. It intensively use in all programs and give directions to program. it can be linked with various conditions in coding.

Watch Video
 
Here is the Syntax for Message Box:

Msgbox(Prompt,[Buttons],[Title],[helpfile],[context])

 

Syntax	Requirement	Description
prompt	Yes	- Its a string expression displayed as the message in the dialog box. Maximum length 1024 characters.
buttons	No	- Type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. Each button has a numeric Value. If omitted, the default value for buttons is 0.
Title	No	- It’s a String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
helpfile	No	- It’s a String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context	No	- Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

The Buttons Argument Setting: You can choose following options for your Message Box.

Settings/Properties	Value	Description
vbOKOnly	0	- Display OK button only
vbOKCancel	1	- Display OK and Cancel buttons
vbAbortRetryIgnore	2	- Display Abort, Retry, and Ignore buttons
vbYesNoCancel	3	- Display Yes, No, and Cancel buttons
vbYesNo	4	- Display Yes and No buttons
vbRetryCancel	5	- Display Retry and Cancel buttons
vbCritical	16	- Display Critical Message icon
vbQuestion	32	- Display Warning Query icon
vbExclamation	48	- Display Warning Message icon
vbInformation	64	- Display Information Message icon
vbDefaultButton1	0	- First button is default
vbDefaultButton2	256	- Second button is default
vbDefaultButton3	512	- Third button is default
vbDefaultButton4	768	- Fourth button is default
vbApplicationModal	0	- Application modal; the user must respond to the message box before continuing work in the current application
vbSystemModal	4096	- System modal; all applications are suspended until the user responds to the message box
vbMsgBoxHelpButton	16384	- Adds Help button to the message box
VbMsgBoxSetForeground	65536	- Specifies the message box window as the foreground window
vbMsgBoxRight	524288	- Text is right aligned
vbMsgBoxRtlReading	1048576	- Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

  
Buttons Return Values: Following are the return values which give information that which button is pressed

 

Button	Integer Value	Description
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

 

 You can share your feedback and post your excel queries in this blog.

Limitations of Excel

Limitations of Excel For Beginners

In previous article we have discussed about Excel and It's features. Now in this article we will talk about Limitations of MS Excel 2010, which helps us to understand excel better.

As we know excel is a spreadsheet application consist of workbooks, worksheets, columns & Cells so while working on excel we should know how many sheets, cells, column etc. we can have in it. 

Following are limitations of excel:

S. No	Particulars	Maximum limit
1	Open workbooks	Limited by available memory
2	Columns	256 columns
3	Column width	255 characters
4	Rows	65,536 rows
5	Row height	409 points
6	Page breaks	1000 horizontal and vertical
7	Sheets in a workbook	Limited by available memory (default is 3 sheets)
8	Colors in a workbook	56
9	Cell styles in a workbook	4000
10	Names in a workbook	Limited by available memory
11	Windows in a workbook	Limited by system resources
12	Hyperlinks in a worksheet	66,530 hyperlinks
13	Zoom range	10 percent to 400 percent
14	Undo levels	16
15	Fields in a data form	32
16	Number of available worksheet functions	329
17	Earliest date allowed for calculation	January 1, 1900 (January 1, 1904, if 1904 date system is used)
18	Latest date allowed for calculation	31-Dec-99

You can share your feedback and post your excel queries in this blog.



My First VBA Code (Macro)

My First VBA Code (Macro)

For Beginners

This Article helps beginners to write their first Macro in VBA.

Watch Video

People thinks writing a Macro/VBA codes is very difficult however this is not true and anybody can learn VBA by follow some Basic instructions.

VBA (Visual Basic for Applications): As it's name suggest it is extension of VB which is designed by Microsoft for Microsoft applications, such as Excel, Access, Word etc. It's a macro programming language, which used to automate excel and other Microsoft application. To Automate Excel you need to write Code (VBA script) in VBA Editor.
 

You can Access VBA Editor by just pressing Alt+F11 (Shortcut Key) or clicking on Visual Basic under Developer Menu Bar.

  

 

Now you will be able to see VBA Editor, where you can write your first VBA script for your Macro.

To write a code you need to insert a Module. To open the Module you need to go to the Insert Menu Bar and then select Module.

Here is your first Module.

Now you can write your codes. However before moving ahead we need to understand composition of VBA procedure.

As VBA is a structured Language hence, we need to follow its basic composition.

Composition of VBA Script:

A VBA module consists of Sub procedures. A VBA procedure is constructed from three types of statements which needs to be write between Sub and End Sub Statement

Executable: These Statement use to perform an Action, the actual task which you want to perform.

Declaration: This statement is used to define procedure, variables, properties, arrays and constant.  

Assignment: Assignment statements carry out assignment operations, which consist of taking the value on the right side of the assignment operator (=) and storing it in the element on the left.

Now we learnt about basic structure of VBA script, Let’s move ahead and write a program.

To begin with, we will create a Message Box Program, following are the Steps.

Steps:

1.    In Module, Type “Sub <Name of your procedure> ()”, Press Enter.

2.    Now Type Msgbox “Hello”

3.    Your module will look like as given below


 

4.    To Execute your program, Press F5

 
         5.    Now you have created your first Macro and it has been executed successfully.

Click Here to download the excel file along with the codes.


You can share your feedback and post your excel queries in this blog.