VBA Macros in Excel.

http://www.analystsdigest.com

Summary of my previous blogs is stated below.

Data Analysis means analyzing the sorted and organized data statistically and logically. The means are various coding languages if data is quite big. For example, VBA Macros in MS – Office. VBA means visual basic for applications. Official name is “Visual Basic, Applications Edition. VBA is the most vast language amongst high level languages.
How to Write Excel Macros in VBA?
1.) Open Microsoft Excel.
2.) Press Alt+F11(VBA IDE will get opened). OR Go to Tools -> Macros -> VBA Editor.
3.)Insert Module. Write VBA code in that Module.
4.) Remember each module must start with subroutine and end with subroutine.
For example -
Sub Macro1()
“Code in between these two extreme lines”
End Sub
Below are the images for Excel file first then VBA Editor after pressing Alt+F11.

Alt+F11

You can directly run macros from the VBA Editor by clicking the play sign on the tool bar Or you can assign the macro to an event say to a button. The event will then be button click. See below.

You can also record a macro if you have a repetitive task to perform on the daily basis. Follow the steps stated below.

1.) Go to View -> Record macro

2.) A window will appear which will ask you to select your macro’s name, click OK to keep it default which is of the form macro1 or macro2 etc, else change it and then click OK.

3.) After clicking OK in the above dialog box your recording will start. Then you just need to perform your daily basis tasks on the excel sheet.  After finishing the tasks
Go to View -> Stop Recording.
The recording helps in the repetitive tasks that are required to be performed on the daily basis. Recording also helps coders in several other ways. For example if we have a task that requires some repetitive work and some non repetitive work, then we can record the repetitive part and code the remaining part.

Basically there are Four ways of writing macros.
1.) Record complete macro as elaborated previously in the blog. This can only handle repetitive tasks.
2.) Record macros in parts. For example if your task comprises of both repetitive and non repetitive parts.
3.) Record Complete Macro and then tweak. For example if the whole task is repetitive but attributes or variables might change.
4.) Write Complete Code. Helps in better understanding of the task and is completely error free and code maintains itself as it is dynamic.
Mostly the third way of coding is used in most organizations where the complete code is recorded and is then tweaked as per user’s need. This saves time because we do not have to apply logic for each and every minute detail. Code is then tweaked in between. Tweaking involves putting loops, conditions etc.
Today we learned ‘How to record a macro?’ My next blog will be focused on the VBA coding manually.
AnalystsDigest

VBA Quiz.

VBA Quiz 1

Which of the following is not a valid Excel object?

i) ActiveWorkbook
ii) ActiveSheet
iii) ActiveCell
iv) ActiveRange

A) i    B) iv    C) iii    D)ii     E)None.

Submit your answers via comment. Refer to my earlier blog at http://analystsdigest.blogspot.in/2014/08/vba-and-its-features.html for guidance.

DATA QUOTES

DATA QUOTES CONTINUED...

Quotes gradually emerge as the things are on rise. Same is with the Big Data. Here are few original Data Quotes by me.

1. Data-Analysis gradually becomes a vital part of Big-Data as Data gets so large and complex that it becomes difficult to process.
2. We are supposed to create Data. Each and every one of us does so. And then we analyze collected data. But if we realize to avoid collecting unwanted data sooner than we can make a difference to the world as a whole.
3. Data Analysis does not make business decisions. Data Analysis just makes the databases or reporting explicit to access corporate information that helps making business decisions.
4. A Dashboard is worth a thousand tables filled with raw data.
5. Data sets on a graphical tree are just like apples on a tree more like a picture, transparent and easy to sum up.
6. Not Always Relevant Information needs to be dug up from Bulk and Complex Data. Sometimes it is apparent.
7. The best way to draw conclusions and make predictions about data is to torture it.
8. If the only problem you have is 'Data', you are bound to study.

VBA and its features -

What VBA Can do?

My earlier blog described about VBA and its capabilities. This blog is focused upon 'Features of VBA and usage'?

VBA is used for writing Macros to Automate Labor-Intensive and Repetitive Tasks and to bring efficiency and 100% accuracy by eliminating manual tasks.

VBA creates User-Defined Functions and sub routines to Achieve Complicated Functionalities. Subroutines are independent entities and never return a value while functions does. A function cannot change the value of the actual argument.

VBA creates Standard Windows Menu/Tool Bars for Interface

VBA can take I/O with External Files. VBA can also perform Database Operations applications like MySQL, Exce., Access etc.

VBA is an object oriented language and has separate object models for different entities. What is an object? 

An Object is an entity or a body such as Worksheet, Workbook, Range, Cell, Chart, Name, etc. which has properties like quantity, name, type. A Group of Similar Objects Share Common Properties, Methods and Events. 

Some Examples are : - 

1. Worksheets("Sheet1") is an Object Referring to the Sheet 1.
2. Range("A1:AZ500") is an Object Referring to a Range starting from A1 till AZ500.
3. Cells(1,1) or Range(“A1”) is an Object Referring to Range “A1”

A property of an object refers to its physical characteristics say quantity, color, type. Examples - 

1. Worksheets.Count (Read Only)
2. Worksheets.Visible = True (Read and Write)

A method of an object refers to the action it performs on a certain event.

Examples

   1. Worksheets.Close
   2. Worksheets.Save

   3. Worksheets.Add
   4. Worksheets.Delete

An event refers to the action performed by the object on that event which may be mouse click or on workbook.save etc. Example - Mouse click, Button click, Mouse Hover etc.

• The basic hierarchy of VBA Excel Object Model -

• The basic hierarchy of VBA Word Object Model -

• The basic hierarchy of VBA Access Object Model -

 

to be continued............

Analysis using VBA in excel Database.

WHAT IS VBA?

As you all know that different databases use different programming languages to deal with data.
VBA is the one for excel and is also used for SQL many a times. VBA has its IDE in excel. Some specific details are as follows : -

1.) VBA is an abbreviation for Visual Basic for Applications.

2.) VBA is Formally known as "Visual Basic, Applications Edition."

3.) VBA is Microsoft's application programming language for Word, Excel, Access, etc. VBA codes also known as macros are created in VBA IDE comes embedded in applications of MS-Office.

4.) Also being implemented in other Microsoft applications such as Visio and is at least partially implemented in some other applications such as AutoCAD...

5.) Visual Basic for Applications is the most vast programming language with a huge syntax store. It is interpreted, object oriented language for Window operating system.

6.) VBA is the language used to program Windows Office applications in order to automate them. VBA is mostly used in excel or access applications of MS-Office. Although other applications can also be automated using VBA for example power point presentation automations.


My Next Blog will be focused on the working of VBA and its functionalities.

DATA QUOTES Continued ...

DATA QUOTES

Quotes gradually emerge as the things are on rise. Same is with the Big Data. Here are few original Data Quotes by me.


1.) A Dashboard is worth a thousand tables filled with raw data.

2.) Data sets on a graphical tree are just like apples on a tree more like a picture, transparent and easy to sum up.

3.) Not Always Relevant Information needs to be dug up from Bulk and Complex Data. Sometimes it is apparent.

4.) The best way to draw conclusions and make predictions about data is to torture it.

5.) Go "SOAIR" on Data.
SOAIR = Sort, Organize, Analyze, Interpret and represent.

6.) If the only problem you have is 'Data', you are bound to study.
 

Programming Languages.

A programming language is a set of instructions written in high level language which is user friendly designed to send commands in syntax forms to a tool or system, especially a PC.

Programming languages can be used to create modules containing several programs or algorithms in different syntaxes to control the behavior of a machine.

There are several programming languages like C, C++, Java, VBA, VB,NET.

All these languages can be used to manipulate data or analyze data. Data is analyzed using several algorithms. But those algorithms are coded in various languages depending on their databases, For example if database is in Excel then VBA is the embedded coding language for which IDE is provided by MS Office itself. VBA is the most vast programming language.