Anthony's Excel VBA Page - Excel Tutorial - Excel Consultant - Excel Consulting

Time 2020-07-17 18:03:57

Web Name: Anthony's Excel VBA Page - Excel Tutorial - Excel Consultant - Excel Consulting

WebSite: http://www.anthony-vba.kefra.com

ID:27385

Keywords:

VBA,Excel,Anthony,

Description:

This page contains Excel and VBA (Macro)tutorial examples on various topics such as finance, mathematics, statistics andother general issues. Users can learn Excel VBA topics range from simple issues such as using VBA recorder to record macro, computing sum, average, medianand standard deviation to advanced issues such as Black-Scholes and Binomial option pricing models, multiple regression, portfolio optimization, probability distribution random numbers generator, Bootstrap, Monte Carlo Simulation, risk analysisand many more. Excel VBA examples and tutorials on this website are being placed into the following categories: Excel VBA Basic Tutorial Series Excel VBA step by step examples and tutorials forbeginners. Shows users how to create VBA programs fromscratch.Beginner can create his or her first VBA application in minutes. Three (3) series in this section. Excel VBA Simulation Basic Tutorial Series Introductory and immediate VBA examples and tutorials. Two (2) series in this section. Excel VBA Statistics and Mathematics Examples Introductory and immediate VBA examples and tutorials. Excel VBA Projects Intermediate and advanced Excel VBA examples on finance, mathematics, statistics andother general issues. Thirteen (13) projects (11 with open source code). Excel on the Web Examples(New) (Archives) Interactive Excel examples on the Web (no VBA). Mostly usingformulas (functions). Excel (No VBA) Statistics and Finance Examples Excel examples without using VBA. Visual Basic for Applications is very similar to the stand-alone Visual Basic programming language. It is the macro language for all of Microsoft's major applications. This home page provides examples in VBA for Excel. One of the features that makes VBA for Excel very powerful is its ability to use "Excel engine" in its programming language. For example, writing a program to invert a matrix can be a very complicated and troublesome. However, one can use the Excel build-in functions to perform such tasks by incorporating the functions into the program. The objective of this website is to demonstrate how to apply Excel VBA in various academic fields such as economics, finance, statistics, andmathematicsand provide step by step examples in programming the projects to help the readers gain a better understanding on how the projects are approached - though some of the coding might seem inefficient in term of programming. The contents of this website should be used for academic purpose only, even though the examples used can also be applying to the real world situation. All the examples can be accessed under the VBA section of this site. Hope you enjoy this site. Please also visit this website's forum section where readers can post their questions and share their knowledge. Anthony P.S. There are tons of examples on this web site and new examples are being added on a regular base. Here are some of the example collections (many free source codes): Excel Business Solutions Consultancy Excel Business Solutions provides spreadsheet and database solutions through Excel automation and business modeling. Each of our consultants has more than 10 years of professional experiences with spreadsheet modeling in various Excel projects. Keywords: Excel Consulting Service, Excel Consultancy, Excel Consultant , Excel Devoloper, Excel Programmer. Creating Your First Macro In this sub section, we will show you how to create your first macro (VBA program). We will use the world classic "Hello World!" example. Recording a Marco this example, we will record a macro that sets the cell background color to light yellow. See the Recorded Syntax Before we run the marco, let's look into the syntax. Run the Recorded Marco Run the recorded macro in the worksheet. Modules and Procedures and Their Scope A module is a container for procedures as shown in our prior examples. A procedure is a unit of code enclosed either between the Sub and End Sub statement or between the Function and End Function statements. Calling Sub Procedures and Function Procedures There are two ways to call a sub procedure. This example shows how a sub procedure can be called by other sub procedures.... Passing Argument by Value or by Reference If you pass an argument by reference when calling a procedure, the procedure access to the actual variable in memory. As a result, the variable's value can be changed by the procedure. Objects and Collections Objects are the fundamental building blocks of Visual Basic. Anobject is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range. Workbook and Worksheet Object A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet. Range Object and Cells Property Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used. Methods and Property Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic of the object. A method is an action that you perform with an object. Assigning Object Variables and Using Named Argument Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments. Objects and Collections Objects are the fundamental building blocks of Visual Basic. An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range. Workbook and Worksheet Object A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet. Range Object and Cells Property Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used. Methods and Property Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic of the object. A method is an action that you perform with an object. Assigning Object Variables and Using Named Argument Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments. Creating and Managing Array This is an essential skill for creating a simulation. Topics in this section cover declare an array, resize an array, manage dynamic array, create multi-dimensional array, and find the array size. Decision Structure - IF and Select Case Contains tutorial using If...Else, If...ElseIf, and Select Case statements. Loop Structure - For...Next and Loops Provides examples on all the loop structures: For...Next with Step, Do...While...Loop, So Until...Loop, and etc. Sorting Numbers in an Array This tutorial shows how to call a sort sub procedure, passes the array to it, and returns a sorted array. Finding Max and Min in Array Extract the maximum and the minimum values in an array using sorting method. Double Sorting - The Secret of Resampling Without Replacement This example shows how to derive unique random values by sorting one array based on another array. Random Number and Randomize One of the most essential 'Must Know' in simulation. Standard Deviation and Mean Two of the mostly used statistic estimates in action. Skewness and Kurtosis Second tier statistic estimates. Percentile and Confidence Interval Compute the simulated values at a specific percentile. Profitablity Compute the probability at a specific value (or profit). Creating a Histogram A tutorial on how to create a histogram by passing array into the histogram sub procedure and returns the frequency distribution from the array. A simple tutorial that shows users on how to compute mean and standard deviation from an array (also a demonstration of creating user-defined functions). This example involves generating multiple unique random numbers from 1 to 54 with resampling without replacement technique by utilizingsorting one array based on another array. What is the probability of getting 3 cards with red hearts and two other cards when 5 cards are drawn from a deck? This tutorial simulates an actual scenario. The probability distribution derived from this simulation happens to be a Hypergeometric distribution. This tutorial shows how to create random numbers from a normal distribution given the standard deviation and the mean, and then computes the confidence interval given the level of significance. Also, a histogram is introduced. This example uses simulation to find the integral (area under) of a normal distribution curve within a specific interval. The example computes the European call and put price based on Black-Scholes option pricing models. Cumulative Standard Normal Distribution is also introduced. The example computes the European call and put price based on Binomial option pricing models. Binomial coefficient is also computed. This tutorial demonstrates on how to obtain the optimal portfolio (highest return with lowest standard deviation) using Harry Markowitz theory. The largest Sharpe Ratio is used to determine the optimal set. The efficient frontier is plotted from the simulated sample. Using matrix algebra by utilizing the Excel functions such as, MInverse( ) and MMult( ), which performs matrix inversion and matrix multiplication, respectively. Use resampling with replacement, a probability distribution for the median is created, along with the standard deviation of the median, which cannot be computed under mathematical formula (since there is none). This example is perhaps the most advanced example so far on this website. It generates multivariate standard normal distribution deviates from correlated variables and then compute the probability from the given z values. A numerical procedure, Jocobi search method is used to derive the Eigenvectors and Eigenvalues. This tutorial is two of the most popular tutorials on this site (guess which is the other one?). By assuming underlying probability distributions (normal, uniform, and truncate normal) of the variables in the profit equation, we get a probability distribution for the profit. It answers the questions like 'what is the chance that we will loss profit' and 'what is the chance that we will make the X amount of profit.' This tutorial contains option sensitivities (delta, gamma, vega, theta, and rho)formulas and source code. Option sensitivities are also know as the Greeks. They measures how sensitive the option price istoward changes in its parameters. All Greeks are available in user-defined VBA functions and in mathematical formulas.

TAGS:VBA Excel Anthony 

<<< Thank you for your visit >>>

Provide free Excel VBA Tutorial, free Excel VBA code and programs, finance and statistics Modeling, and Excel Consulting. Anthony Sun is an Excel Consultant and data analyst specialized in Excel programming and database programming.

Hot Websites