15,99 €
This work was born from the necessity found by the author to solve problems of various kinds during her studies and work. The large amount of data and the repetitive processing required an automated procedure that could be applied in any context. The author, being a programmer and being an expert in Pascal, Cobol and Javascript, searched for the most suitable solution and found it in the Excel VBA. This document is intended to introduce users to programming in general and to programming in Visual Basic for Applications and Excel in particular. VBA is simple and can be used on existing Excel files, quickly processing a large amount of data. The only tool we need is Excel.
The book may not contain everything you should know about programming in VBA, but it does point the finger at the key points to create some programs which are explained in the various chapters. The content is divided into two parts one which explains programming in general and the second which explains programming in VBA with concrete examples of programs. In this latest version, more complex electronic procedures have been added that process various Excel files at the same time that can be used for warehouse management, for the management of work orders, for the management of payrolls and in all those problems where it is necessary. create summary reports of input data in different files. Obviously we will not write all the possible examples because they are infinite,
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
PROGRAM IN VBA
Summary
Introduction
PART I.: The basics of programming
Chapter 1: Understanding and solving problems
Understanding a problem and breaking it down into simple operations
Identify variables and constants
What does pseudo-code mean
Chapter 2: The logic of programming
The variables
Transform a sequence of operations into instructions
The flow chart
PART II: Programming in VBA
Chapter 3: The VBA
What is VBA and what can it do?
How to access VBA
Chapter 4: Program in VBA
Variables in Excel sheets
Macros in Excel
Create a button to activate a macro
Chapter 5: Examples of programming in VBA
The sum of n numbers
The average of n numbers
Count values
Sum of the values in a given range
Sum of values that meet certain conditions
Chapter 6: Using Multiple Sheets to Solve Complex Business Problems
COUNT CONTACTS
REMUNERATION CALCULATION
Chapter 7: Summary of Multiple Excel Folders in One File
PROCEDURE FOR CHECKING TWO LISTS OF DATA
PROCEDURE INSERTING PRACTICES IN A SINGLE CONTROL FILE
BUDGET IMPLEMENTATION PROCEDURE WITH VARIOUS ORDERS IN VARIOUS EXCEL FILES
Epilogue / Conclusion
FROM THE ANALYSIS OF THE PROBLEM TO THE PROGRAM IN VBA
FROM THE SIMPLE TO THE MOST COMPLEX PROGRAMS
Of
OLGA MARIA STEFANIA CUCARO
© Copyright 2021 by Olga Maria Stefania Cucaro - All rights reserved.
It is not permitted to reproduce, duplicate or send any part of this document electronically or in print. Copying of this document is strictly prohibited. All the programs included in this book are also copyrighted by the author / creator of the work.
Dedicated to:
To my mother who has always believed in me and to my IT professors who made my working life easier
Summary iv
Introduction 1
PART I.: The basics of programming 2
Chapter 1: Understanding and solving problems 3
Understanding a problem and breaking it down into simple operations 3
Identify variables and constants 4
What does pseudo-code mean 4
Chapter 2: The logic of programming 5
The variables 5
The conditions 6
The cycles 7
Transform a sequence of operations into instructions 8
The flow chart 9
11
PART II: Programming in VBA 13
Chapter 3: The VBA 14
What is VBA and what can it do? 14
How to access VBA 14
Chapter 4: Program in VBA 16
Variables in Excel sheets 16
Macros in Excel 17
Create a button to activate a macro 18
Chapter 5: Examples of programming in VBA 20
The sum of n numbers 20
The average of n numbers 23
Count values 27
Sum of the values in a given range 31
Sum of values that meet certain conditions 37
Chapter 6: Using Multiple Sheets to Solve Complex Business Problems 42
COUNT CONTACTS 42
REMUNERATION CALCULATION 52
Chapter 7: Summary of Multiple Excel Folders in One File 59
PROCEDURE FOR CHECKING TWO LISTS OF DATA 59
PROCEDURE INSERTING PRACTICES IN A SINGLE CONTROL FILE 64
BUDGET IMPLEMENTATION PROCEDURE WITH VARIOUS ORDERS IN VARIOUS EXCEL FILES 96
131
Epilogue / Conclusion 132
This work was born from the necessity found by the author to solve problems of various kinds during her studies and work. The large amount of data and the repetitive processing required an automated procedure that could be applied in any context. The author, being a programmer and being an expert in Pascal, Cobol and Javascript, searched for the most suitable solution and found it in the Excel VBA. This document is intended to introduce users to programming in general and to programming in Visual Basic for Applications and Excel in particular. VBA is simple and can be used on existing Excel files, quickly processing a large amount of data. The only tool we need is Excel.
The book may not contain everything you should know about programming in VBA, but it does point the finger at the key points to create some programs which are explained in the various chapters. The content is divided into two parts one which explains programming in general and the second which explains programming in VBA with concrete examples of programs. In this latest version, more complex electronic procedures have been added that process various Excel files at the same time that can be used for warehouse management, for the management of work orders, for the management of payrolls and in all those problems where it is necessary. create summary reports of input data in different files. Obviously we will not write all the possible examples because they are infinite,
In the previous example we can identify four variables, three of input (input) and one of output (output). In particular A, B and C are the input variables, while Sum is the output variable. If we used an intermediate variable we would call it a working variable. The difference between variables and constants is essential, a constant says the word itself and already set at the beginning of the program and does not change during processing.
Variables are divided into integers, reals and booleans. Integer variables contain integers, real variables contain real numbers and Boolean variables contain two states (true or false)
Why did I write contain? Precisely because the variable is like a container and contains what we indicate.
In all programming languages the first step is the definition of the variables which makes the computer able to recognize them.
In our simple little program, the sum of three numbers A, B, C and Sum are numeric variables that can be integers at the programmer's choice. Obviously we must pay the utmost attention in defining the output variable which if it contains the result of an arithmetic operation on the input variables must have the same definition as these.
The pseudo-code allows programming in a general language which is then declined in the individual languages by the programmer. We can simply say that a pseudo-language is a generic programming that becomes specific with the choice and use of the single language. If a programmer is a beginner, it is better to start by writing a pseudo-code first and then transform it into the actual program in the chosen language.