anthony-vba.kefra.comAnthony's Excel VBA Page - Excel Tutorial - Excel Consultant - Excel Consulting

Anthony’s Excel VBA (Macro) Tutorial T his page contains Excel and VBA (Macro) tutorial examples on various topics such as finance, mathematics, statistics and other general issues. Users can learn Excel VBA topics range from simple issues such as using VBA recorder to record macro, computing sum, average, median and 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 analysis and many more. E xcel 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 for beginners. Shows users how to create VBA programs from scratch. 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 and other 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 using formulas (functions). Excel (No VBA) Statistics and Finance Examples Excel examples without using VBA. V isual 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. T he objective of this website is to demonstrate how to apply Excel VBA in various academic fields such as economics, finance, statistics, and mathematics and 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. H ope you enjoy this site. Please also visit this website’s forum section where readers can post their questions and share their knowledge. AnthonyP.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): Copyright © Anthony Sun All Rights Reserved. Standard Deviation | Median | Mean | Skewness and Kurtosis | Lotto Number Generator | Playing Card Probability and Hypergeometric distribution | Monte Carlo Integration and risk analysis | Black-Scholes Option Pricing Model - European Call and Put | User-defined customized function | Binomial Option Pricing Model | Portfolio Optimization | Multiple Regression | Bootstrap | Multivariate Probability Distribution | Monte Carlo Simulation | Option Sensitivities (Greeks) | Frequency Histogram | Creating and Managing Array | finding Max and Min in Array | decision structure | If and Select Case | Binomial Coefficient | Sum Number in an Array | sorting | resampling without replacement | factorial | randomize | random number generator | Cumulative Standard Normal Distribution | declare an array | resize an array | manage dynamic array | create multi-dimensional array | find the array size | For...Next with Step | Do...While...Loop | Do Until...Loop | call a sort sub procedure | Percentile and Confidence Interval | Chi-Square | F Distribution | normal | Student-t | Lognormal | Log Pearson III Distribution | Gamma Distribution | Beta Distribution | Hypergeometric Distribution | Triangular Distribution , Binomial Distribution | Multiviate Standard Normal Distribution | Numerical Searching Method | Newton-Ralphson | Secant method | Bisection method | Implied Volatility | Creating a Histogram | Option on asset with cash dividend | option on asset with continuous dividend (stock index) | option on futures | option on currency . 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. Excel VBA Basic Tutorial Series Excel VBA Basic Tutorial Series 1 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 In 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. Excel VBA Basic Tutorial Series 2 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. Excel VBA Basic Tutorial Series 3 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...