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.

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

Qualitative vs Quantitative Research.

Hello All,
Wait! Is Bulk and Complex Data killing your time and efforts. Go through the following article to reduce your efforts to a minimum level.
The word torture in the picture above means processing the data using some algorithms and tools. There are two ways of analyzing the data or doing research work over data. One is Qualitative Approach while other is Quantitative.
Differentiating them below -

	Qualitative	Quantitative
SUPPOSITION(theory)	Large	Small
DETAILS	Complete picture	Steady/Focused
Type of Analysis	Exploratory	Conclusive

Qualitative analysis goes in phase 1 when data is gathered and maintained in the form of a dashboard or graphical manner. Quantitative analysis comes in phase 2 when data is bulk and complex, maintained in separate entities. Entity here is one data table full of records.
Qualitative analysis is exploratory and/or investigative. Findings are not conclusive and cannot be used to make generalizations over present data. It only helps in deducing the facts in initial phase, helps in further proceedings.
On the other hand Quantitative analysis is used to recommend a final course of action. Conclusive in nature.
For Example – “”A lawyer “X” who is say a criminal defense lawyer. X discusses the case with his client, cross examines the witnesses, tries to negotiate a deal between the client and the prosecutor so as to solve some amount of case outside the court. All this happens in the initial phase.
X has the data fed to him by his client and several other witnesses. X also has his own legal data, rules, procedures. X follows Qualitative approach in the initial phase so as to reach to some decisive understanding.
This approach helps X in trying negotiations. If prosecutor is unwilling to negotiate then X manipulates the data sets again just to figure out some way of getting the sentence of his client reduced.
After the initial phase X gets time from the court to prove his client’s innocence to some amount. He then follows a Quantitative approach in which he goes through the situation and details in a focused manner and prepares some expert witnesses to make his case strong and may also hire some investigators to bring more evidence again ‘data’ and thus the prosecutors case may seem less credible and results might go in favor of X’s client””
“So we saw X gets Input which is Raw Data from his client, witnesses, observations, legal artifacts and after analyzing it thoroughly he concludes the case from his side.”
Qualitative analysis is Non Statistical while Quantitative analysis is Statistical.

Prateek

Data Analysis and Interpretation

This blog will differentiate Data Analysis and Data Interpretation.
Data analysis is making a summary of gathered data. This is what a Data Scientist does.
Data Interpretation is extracting the meaningful information out of the summarized Data. After research Data Scientist does this too.
Data analysis starts just after collecting the data. The data is divided into analytical units, modules based on patterns. For Example “A class teacher maintains a register of attendance for two classes. At the end of the month she finds out the worst offenders. She divides the data into two sets first. Class A and Class B. The two sets become two separate analytical units which are then analyzed separately. Further she divides each set into different modules based on patterns such as a student remains absent every Tuesday. She then calls student’s parents to ask for his/her absence on a particular day every week.”
So this is the traditional way of managing and making summary of the data. So In the above example, Teacher collects data of two classes for the whole month then she analyzes it before interpreting it to the offender’s parents.
This way of managing the data is manual which can be automated as per the requirements using several coding techniques like VBA, VB.NET, Java etc.
So the 4 basic steps of processing Data are concluded as follows :  -
1.) Collecting Data (Surveys, Reports, Gather etc etc)
2.) Analyzing Data (Analyzing Causes. effects and Consequences)
3.) Summarizing Data (Bringing Data into Readable Format)
4.) Interpreting Data (Displaying Findings & Stating what’s missing)
My Next Blog will focus on Qualitative vs Quantitative Research.

SCOPE OF DATA ANALYSIS

The scope of data analysis has increased by leaps and bounds. Data analysis is becoming important across all industries and if used wisely, it will prove beneficial.
Data analytics is widely used by many business organizations to better understand their customers. Traditional methods such as reports and dashboards are running obsolete. Businesses are not only looking at data analysts to only understand and interpret data, but also to work with the organization’s leadership teams to facilitate strategic decisions. 35% of data analysts work with the management, 33% with the IT Admin, and 36% as programmers while 23% as graphic designer.
Once there was a boom in IT and telecom domain, the time again has come for boom in analytics domain. Demand for Data analysts will be there in each and every small or big company.

What is Data?

My Earlier Blog was focused on Data Analysis which is the main subject of Analyst’s Digest.
This blog explains the meaning of data. People must have learned the basic definition that Data is a collection of records. My question is what are these records, what constitutes a record?
Record is nothing but certain pieces of things set in writing or typed or stored in some kind of storage devices for later references. These records when pile up forms data. “BIG DATA”. Lets get into Data then.
Data is a collection of Facts be they assumed or known & Statistics such as values or measurements. More precisely Data consists of texts, numbers, descriptive or non descriptive records, boolean values that are true or false, comments, status, etc.
Moreover Data can be qualitative (that is descriptive, argumentative) or quantitative (Statistical, Numeric). Data takes various forms when we look into its most basic format which is raw. Data in quantitative form may be discrete or continuous. The Continuous data is easy to simplify into meaningful form.
Till Now we have talked about various forms of data. But readers might be wondering that where does this data come from? What leads to such big and cluttered data?
Data gets collected by several means or say procedures.
1.) Observation or Survey. For example – Scientific Experiments, Survey of population etc.
2.) Collecting Information. For Example – Attendance in a school, Work Status in a corporate.
3.) Providing Solutions and Services – For Example – Keeping Various trackers in a corporate company for the work delivered to a customer.
4.) Emailing – Gmail , Yahoo etc. Mails = Data.
So Data can be biological, environmental, Scientific, agricultural, academic etc.
My next blog will highlight some broader aspects of data analysis. Till then keep on playing with data and try to come up with new and unknown algorithms, models to filter meaningful information.

DATA ANALYST FLOWCHART. I/P and O/P .

ANALYTIC’S STUDY

The Prime task is to understand what is a data analyst and what he/she does?
Lets ponder over verb first.
Data Analysis means analyzing or inspecting the data, be it in bulk or in insignificant amount. The Data analyst after proper analyzing the data deduces the conclusion and develops or brings the end result in a format which is immensely meaningful and readable.
While the Data Analyst is a medium, the Data Analysis is a process that follows several steps to convert Data into a meaningful information.
I would not hesitate in calling Data Analyst a Tool who runs several algorithms and applies Data Analysis Models & performs various mathematical calculations  to extract and classify information from unstructured data.