EXCEL VBA : A Comprehensive, Step-By-Step Guide On Excel VBA Finance For Data Reporting And Business Analysis: 4

By Peter Bradley

Have you always wanted to automate the analysis that you perform at work?

Are you trying to use different Microsoft tools to do the same?

Do you want to know how you can use VBA to automate these processes?

If you answered yes to those questions, you have come to the right place.

Over the course of the book, you will gather information on some of the most important features of Excel VBA including the Macro Recorder. You will learn how to use the different data types and other features of Excel.

There are some samples that you can use to understand how you can analyze different types of data in your tables. You will learn more about:

• Some facts about Excel VBA
• Some issues with Excel spreadsheets, and solutions to handle those errors
• How to work with Vlookups in VBA
• How to work with Pivot tables in VBA

If you have some knowledge about Excel VBA and want to use it to analyze information and create financial reports, Grab a copy of this book Now.

• Language: English
• Publisher: Peter Bradley
• Release date: Sep 10, 2019
• ISBN: 9781393071068

Peter Bradley

Peter Bradley was the Labour MP for The Wrekin between 1997 and 2005. More recently, he co-founded and directed Speakers’ Corner Trust, a charity which promotes freedom of expression, open debate and active citizenship in the UK and developing democracies. He has written, usually on politics, for a wide range of publications, including The Times, The Guardian, The Independent, The New Statesman and The New European.

Book preview

Peter Bradley

Introduction

Chapter One: Introduction to VBA

What can you do with VBA?

Common Uses of VBA

Automating Documents

Customizing Application Interfaces

Adding new application features

Chapter Two: The IDE

Project Explorer

Properties

Code

Looking at the VBA Toolbox

Starting the Visual Basic Editor

Using Project Explorer

Working with special entries

Using the Properties window

Understanding property types

Getting help with properties

Using the Code Window

Opening an existing Code window

Creating a new Code window

Chapter Three: VBA, A Primer

Macro Recorder

Record a Macro

Run the Recorded Macro

See the Macro

Macro Storage and Security

How to Add a Trusted Location

How to Enable Macros Outside a Trusted Location

Chapter Four: Data Types

Using strings for text

Understanding strings

Adding strings together with + or &

Using character codes

Using numbers for calculations

Understanding the numeric types

Using Boolean values to make decisions

Working with Operators

Chapter Five: Decision Making Statements

If...Then Statement

If...Then...Else statement

If...Then...ElseIf statement

Using the IIf function

Chapter Six: Loops

Do While...Loop statement

Do...Loop While statement

Do Until...Loop statement

Do...Loop Until statement

For...Next statement

For Each...Next statement

Chapter Seven: Arrays

Structured Storage

Array Types

VBA Array

Step 1 – Create A New Workbook

Step 2 – Add A Command Button

Step 3 – Save the File

Step 4 – Write the Code

Example to Enter Student’s Marks

Without An Array

Using an Array

Example with Loops

Sorting an Array

Example for Creating a Two-Dimensional Array

Chapter Eight: How to Manipulate Data in Excel

How to Analyze and Manipulate Data on a Spreadsheet

How to Construct an Array and Locate the Range

Different Ways to Manipulate Data

Count the Number of Sheets in a Workbook

Filter by Using Advanced Criteria

Getting Values from Another Worksheet

Adding Named Ranges to Specific Sheets

Copy an Entire Row for Pasting

Inserting a Column into a Worksheet

Insert Multiple Columns into a Worksheet

Select a Specific Sheet

Chapter Nine: Working with Excel Workbooks and Worksheets

The Workbook Collection

The Worksheet Collection

Charts Collection

Chapter Ten: Automating Processes Using VBA

The Macro Mindset

Understand the Context of your Automation Project

Product Code Lookup Procedure

ProdCodeLookup2 Breakdown

Tips to Deal with VLookups in VBA

Data Analysis

Using Pivot Tables in VBA

Learn to Use Cell Referencing

Efficiently Allocating Your Time

Chapter Eleven: Error Handling

Understanding compile errors

Understanding run-time errors

Understanding semantic errors

Conclusion

Sources

––––––––

© Copyright 2019 -Peter Bradley – All rights reserved.

The contents of this book may not be reproduced, duplicated or transmitted without direct written permission from the author.

Under no circumstances will any legal responsibility or blame be held against the publisher for any reparation, damages, or monetary loss due to the information herein, either directly or indirectly.

Legal Notice:

This book is copyright protected. This is only for personal use. You cannot amend, distribute, sell, use, quote or paraphrase any part or the content within this book without the consent of the author.

Disclaimer Notice:

Please note the information contained within this document is for educational and entertainment purposes only. Every attempt has been made to provide accurate, up to date and reliable complete information. No warranties of any kind are expressed or implied. Readers acknowledge that the author is not engaging in the rendering of legal, financial, medical or professional advice. The content of this book has been derived from various sources. Please consult a licensed professional before attempting any techniques outlined in this book.

By reading this document, the reader agrees that under no circumstances is the author responsible for any losses, direct or indirect, which are incurred as a result of the use of information contained within this document, including, but not limited to, —errors, omissions, or inaccuracies.

Introduction

VBA is an amazing tool that many organizations have begun to use to perform some tasks in a few minutes or a few seconds. For instance, you can use VBA to analyze some information or extract some information from a given data set. You can also perform some special types of data analysis. When you write a macro in VBA, you can be certain that you have mastered your work.

If you want to learn how you can use VBA for finance and data analysis, you have come to the right place. Over the course of the book you will gather information on what VBA is, and what different parts of VBA you need to be aware of. You will learn more about the data types, the modules and other functions you can use to automate tasks.

Thank you for purchasing the book. I hope you gather all the information you are looking for.

Chapter One: Introduction to VBA

Visual Basic for Applications or VBA is a programming language, which is compatible with most Microsoft Office Products, including Excel. In other words, you can use VBA to develop programs in Excel. These programs will make Excel operate accurately and very fast.

What can you do with VBA?

Most people use Excel for a million different reasons. Here are a few examples:

•  Forecasting and Budgeting

•  Analyzing data