Central Gippsland: the home of excelatfinance.com [Photo: "smoke" January 2013]
XLF :: Contents |
Google page search
Windows page search
With link descriptions visible, use Windows Ctrl + F to search. Report errors and omissions to ioconnor@excelatfinance.com.
Contents structure
Some items are listed more than once, and may be included as Excel/VBA and also Finance topics
-
XLF Part I :: Spreadsheets
- Excel resources
- Excel - workbooks and worksheets
- Function keys - Excel function keys - F1, ... F12. Useful keys identified
- Dialog box launcher - a brief description of the Excel ribbon - dialog box launcher
- Name box - examples of common uses of the Excel Name Box
- Reference styles - reference styles. A1 and R1C1. Switching styles. Relative, absolute, and mixed. Includes practical examples and worksheet files
-
Add name relative
- explains the concept of relative offset defined names, with examples. Also provides VBA code to add common relative offset names to a workbook. xlfAddNameRelative
Includes: RefersToR1C1:="=!RC[-1]", relative address on any sheet of active workbook -
Add name scope
- provides VBA code using the Names.Add method - to demonstrate Workbook scope and Worksheet scope
Includes: .Names.Add Name:="WBscope"; With ActiveWorkbook; With ActiveWorkbook.Worksheets("Sheet1") -
Name edit - Excel Mac
- xlf QandA - HOW DO I EDIT THE ADDRESS OF A DEFINED NAME IN EXCEL FOR MAC WITHOUT HAVING TO CREATE A NEW NAME?
Includes: Excel Mac Define Name dialog box, and video demonstration :: name a range, add more data, then edit the name - range of cells - to expand the dimension -
Cells: worksheet - active → current region
- Worksheet cells - Active cell, Selection, and Current region.
Includes: Single cell, multi-cell, identification, and navigation keys. Status bar - Ready mode, and Point mode -
Cell # errors
- Excel errors with examples and xlsx file.
Includes: #REF!, #NULL!, #DIV/0, #N/A!, #NAME?, #NUM!, and #VALUE!
- Excel - WB and WS styling
-
Add blue banner
- VBA code to add the xlf blue banner background to a worksheet
Includes: Interior.Color, RGB, Selection.Resize, -
Add logo to banner
- VBA image from web link to the xlf blue banner background on a worksheet
Includes: Shapes.AddPicture, msoFalse, msoTrue, Picture.Insert, Shapes collection, Shape object, msoPicture, msoLinkedPicture
Syntax: AddPicture, AddPicture2, and ScaleWidth
-
Add blue banner
- VBA code to add the xlf blue banner background to a worksheet
- Excel - functions
- Logical functions Includes: WS IF, AND, OR XOR, TRUE, FALSE, and NOT
-
Char function
Includes: characters 1 to 32 (non printing), characters 33 to 127 (alpha numeric), and characters 128 to 255 (extended)
Syntax: Worksheet: Indirect, Offset -
Indirect function
Includes: an Excel module with examples of the INDIRECT function (two stage) reference, and a direct reference (one stage). INDIRECT allows R1C1 reference in an A1 workbook, and A1 reference style in an R1C1 workbook
Syntax: Indirect - Match function Syntax: Match
- Offset function Syntax: Offset
-
Offset function [height] parameter
Includes: a worksheet to demonstrate the OFFSET function height parameter with negative and positive values. Although Microsoft's syntax states that height must be a positive value, the function has a bug that allows negative values. A comparison is provided
Syntax: Offset -
Text function
- this module demonstrates the Excel TEXT function with application to dates, percentages, currency formats, and numbers.
Syntax: Worksheet: Char, Date, Dollar, Text, Today, Year - Text function - decimal alignment [QandA] TEXT function decimal alignment "???.???". Mono space font ensures alignment
- Standard normal probability tables
-
Excel functions with array arguments
- a list of WS functions with explicit array arguments.
Syntax: Worksheet (CSE): MDETERM, MINVERSE, MMULT, and TRANSPOSE
Syntax: Worksheet (Enter): COLUMNS, CORREL, COVAR, COVARIANCE.P, COVARIANCE.S, ROWS, SUMPRODUCT, FTEST, F.TEST, PEARSON, TTEST, T.TEST
- Excel - workbooks and worksheets
- Numbers: Data, Dates and Dollars
- Data - manipulation
-
Decimal place KB short cut [QandA]
- change the number of worksheet decimal places from the keyboard. Alt H 0, Alt H 9. AdjustDPplus macro
Syntax: VBA Functions: Address, Evaluate, and NumberFormat. WS Functions: Cell, IsNonText, IsNumber - Stock price volume data - web download - example - how to download price data from Yahoo finance. Step by step procedure, with save to a comma separated value (CSV) file
- Text Import wizard :: 2016 on - the Excel 2016 "Text Import Wizard" was replaced by "Get & Transform" in version 1704. Activate the Get Data > Legacy Wizards > From Text (Legacy) to avoid creation of a WS table
-
Data validation w/ multiple lists
- module that demonstrates the use of data validation with multiple level Source lists. Related material: the xlf Stock Analyser project
-
Out of data alert
- create an Out of Data alert using conditional formatting. Related material: the xlf Stock Analyser project
-
Interpolation - linear NA → Formula
- this module demonstrates linear interpolation. Identify each NA error block, then replace each NA with R1C1 formula
Includes: TDCs % and &, IsNA, IsNumeric, Offset, WorksheetFunction, Set, Watches Window, Application.CutCopyMode, and PasteSpecial -
Interpolation - last known value (LKV)
- this module describes the last know value (LKV) interpolation method with application to situations where price-volume data points are missing from the data set
-
M data sheet dynamic range [EandA]
- this document is intended for use by participants in the 90045 session series. It is of limited use if read out of context. Thew exercise includes the missing data interpolation worksheet (named M Data dev in this version) and development of a text based dynamic range name
Includes: Excel functions AND, IF, INDIRECT, ISBLANK, NOT, and INDIRECT(INDIRECT...) when used in a Data Validation environment -
Vector to array
- VBA sub procedure to link an array to a row vector, using cell formulae in the target.
Includes: For...Next loop. CurrentRegion, FormulaR1C1, Cells property. -
Arrays and vectors
- worksheet arrays and vectors. The array as a data structure, and mathematical element wise array operations
Includes: Scalar, column vector, row vector, and multidimensional arrays. Curly braces, and element wise operations with examples (the Carrot Washer)
-
Decimal place KB short cut [QandA]
- change the number of worksheet decimal places from the keyboard. Alt H 0, Alt H 9. AdjustDPplus macro
- Data - analysis
-
Data analysis - subtotals
- subtotals, manual, single, multi and nested. Subtotal format, and copy visible cells. Subtotal function - syntax, and as a dynamic link to labels
Includes: Average, Count, Counta, Max, Min, Product, StDev. StDevP, Sum, Var, VarP
-
Data descriptive statistics, and covariance
- stock price log returns, variance and standard deviation.
Includes: Statistical functions, ATP descriptive statistics, and covariance. COVARIANCE.P, COVARIANCE.S. Full covariance matrix with dynamic reference COVARIANCE.S and INDIRECT
-
Count decimal places in a text string
- this module describes the last know value (LKV) interpolation method with application to situations where price-volume data points are missing from the data set
Syntax: VBA - InStr, Len, Replace, and Rtrim -
Count cells with error values
Use IS functions (ISERROR, ISERR, ISNA), and COUNTIF to count error cells in an Excel worksheet.
Includes: COUNTIF, IF, ISERROR, ISERR, ISNA, SUMPRODUCT and double negation -
Excel 2010 Power Query → Excel 2016 Get and Transform
- an example of Data tab Get and Transform (data) items on the ribbon, plus examples of all Excel 2010 and Excel 2016 ribbon tabs!
-
Data analysis - subtotals
- subtotals, manual, single, multi and nested. Subtotal format, and copy visible cells. Subtotal function - syntax, and as a dynamic link to labels
- Dates
- Date comparisons - different spreadsheet applications - a comparison of dates and serial numbers across three spreadsheet applications - MS Excel, LibreOffice Calc, and Google Sheets. Includes the 1900 leap year error, the spreadsheet epoch, and negative serial dates
-
A list of ASX and NASDAQ non trading days
- a list of non trading days calendar dates for the Australian Stock Exchange (ASX) and the USA NASDAQ Stock Market (NASDAQ) - stored in an Excel dynamic range - OFFSET and COUNT functions. Custom date format
Syntax: worksheet - OFFSET, and COUNT
-
Weekdays to trading days - using the advanced filter
- Excel based - apply the advanced filter to a weekday list to hide public holidays.
Includes: COUNTIF function, series, dates, custom function, advanced filter, criteria range, business days, weekdays, criteria range formula
Syntax: worksheet - COUNTIF
-
A list of business days with Sunday to Thursday workdays
- How to create a list of business days for regions that have a Sunday to Thursday workweek. Includes a case for Johor state in Malaysia
Includes: COUNTIF function, series, dates, custom function, advanced filter, criteria range, business days, weekdays, criteria range formula, and Friday - Saturday weekend
Syntax: worksheet - WORKDAY.INTL, and WEEKDAY function
-
Business (trading) day, next or previous
- demonstrates how to determine if a date is a business day, and if not return the date of the next or previous business day. Uses the Excel WORKDAY function
Syntax: Excel - DATEVALUE, EDATE, EOMONTH, TEXT, WORKDAY, and WORKDAY.INT -
The DateV dynamic range
- part of the stock analyser project - shows how to construct a dynamic range date vector. This is a core component in the moving data windows that drives the descriptive statistics, and dashboard charts in the xlf Analyser Project
Syntax: worksheet - EDATE, FIND, LEFT, SEARCH, and WORKDAY
-
VBA time concepts
- includes examples of TimeSerial, TimeValue, and Time functions, plus the VBA Date data type
Syntax: VBA - TimeSerial, CDbl, TimeValue, and Time -
Is date within range of dates
- FY_17 - a VBA function that checks if a date is within a range of dates. Returns Boolean TRUE or FALSE.
Includes: If...Then construct with two logical tests. AND operator. VBA DateValue function
-
Adding an ordinal indicator to the TEXT function
- include an array constant of the form {1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"}, and use this as the range_lookup argument to a VLOOKUP function..
Includes: WS Text function, and Vlookup with the array constant
-
Trading date function :: ASXTD2016(Dte) [EandA]
- an Exercise with Answer - write a Private Function procedure that determines if a particular date is a trading day for the Australian Stock Exchange …
Includes: Dates in Array type String, Dates in Array type Integer, Dates in Array type Date. CVErr, DateSerial, DateValue, IsError, Split, Time, LBound, UBound, vbSaturday, vbSunday, Weekday
- Dollars
-
Dollars to euros
- format a number with a comma decimal separator (decimal comma) when the WB is set to dot decimal separator (decimal point)
Syntax: WS Functions: REPT, SUBSTITUTE, and TEXT - Rate of return - discrete returns, net returns, gross returns, log returns, cumulative returns, with sample worksheet - download from Excel web app
- Price to return [EandA] - an Exercise with Answer - 1. Write a private function named P2R with VBA caller, and 2. Write a UDF function named Price2Return with Excel (WS) caller. Components: Option Base 1 declaration, ascending and descending order of the price vector or array, and aspects of the array dimension
-
Interest - simple and compound
- a summary of the simple and compound interest, incorporating algebraic formulae, examples, and Excel equations and functions where available.
Syntax: WS Functions: FV, PV, and ACCRINT - Interest rate parity - INDIRECT and DIRECT foreign exchange quotes - compares calculation of the exchange rate forward premium (discount) for AUD/USD and USD/AUD ISO quotes with domestic and foreign interest rates - Australian perspective
- Interest rate risk - gap analysis - Rate sensitive assets (RSA) and rate sensitive liabilities (RSL). Interest rate risk (IRR) measurement and reporting. Gap analysis with Excel based spreadsheet, bucket end points, mid points, quasi delta NII, and actual annual delta NII.
- Rules of exponents - rules of exponents material used in the xlf presentation series. This page includes numerical examples, and keystrokes for the Hewlett Packard 12C financial calculator.
-
Dollars to euros
- format a number with a comma decimal separator (decimal comma) when the WB is set to dot decimal separator (decimal point)
- Data - manipulation
- Charts and the User Interface
- Price Volume combo charts
-
Price volume chart [1] - Excel 2013
- create a price volume (combo) chart from a stock data time series.
Includes: Chart features - the date axis includes non trading days; a price series Currency format and volume series custom Million format are applied
-
Price volume chart [2] - Excel 2013
- create a price volume (combo) chart from a stock data time series.
Includes: Chart features - the date axis excludes non-trading days; a price series Currency format and volume series custom Million format are applied
-
Price volume chart [3] - Excel 2010
- Create a price volume chart from a stock data time series.
Includes: Chart features - the date axis excludes non-trading days; a price series Currency format and volume series custom Million format are applied
-
Price volume chart [1] - Excel 2013
- create a price volume (combo) chart from a stock data time series.
- X-Y (Scatter) charts
-
X-Y scatter plot of correlated random numbers
- Random number generator - using volatile functions - with number of observations n switch. Dynamic range as a statistical data reference. Dynamic range as a chart series. Creating an X Y scatter chart
Includes: WS MIN, MAX, AVERAGE, VAR.S, STDEV.S, SKEW, COUNT, MAX, COVARIANCE.S, CORREL, COVARIANCE
-
X-Y scatter plot of correlated random numbers
- Random number generator - using volatile functions - with number of observations n switch. Dynamic range as a statistical data reference. Dynamic range as a chart series. Creating an X Y scatter chart
- Other charts
- Excel 2013 Power Map ribbon group - an example of Power Map with 3D maps with data from the 100 data records set
- Excel 2013 Power View - an example of Power View and Power BI desktop with data from the 100 data records set
- Battery chart - contains the VBA code to add a 'battery chart' to a WS. Technique: the Shapes.AddChart2 method
- Thermometer chart - contains the VBA code to add a 'thermometer chart' to a WS. Technique: the ChartObjects.Add method
- User interface examples
-
WorkSheet elements 1 - shapes
- add a Rectangle and other elements to the drawing layer, apply shape format, and text format. Include a WS hyperlink, Protect Sheet, and apply a password
Includes: Drawing tools, Shapes, Fill and Outline. TextBox elements - Graphic Tools, Signs and Symbols, Rotate and Hyperlink -
WorkSheet elements 2 - add animated line element
- to code an animated line element on a worksheet
Includes: WS Basic Shapes > Line, VBA Shapes, Shapes.AddConnector, BeginArrowHeadStyle, EndArrowHeadStyle, msoArrowHeadOval, msoArrowHeadTriangle, Application.Wait, unit circle - quadrants, clock quarters, WS functions: Radians, Cos and Sin -
WorkSheet elements 3 - add oval shape
- to code a circle element on a worksheet
Includes: WS Basic Shapes > Oval, VBA Shapes, AddShape, msoShapeOval, Fill.Visible, Line.Weight, ForeColor.Brightness, and ThreeD.BevelTopType msoBevelCircle -
WorkSheet controls - dynamic chart interface [EandA]
- an Exercise with Answer - develop an on sheet interface - three chart types, chart location, and dynamic chart series vectors
Includes: Form controls - TextBox, and Group Box, ActiveX controls - CheckBox, OptionButton, and Image. Chart Callers for Form and ActiveX.
Syntax: VBA - procedures :: cmdCharter_Initialize, HideCaller, UnHideCharter, chart dates and return vector, and chart updater. ChartInterfaceReset -
WorkSheet controls - default values and passing arguments
- develop a box sequence interface using form controls.
Includes: Form controls - TextBox, Button and Check Box. Pass the Check Box value to the procedure ByVal as Boolean. Code a quasi click event for the BoxSequence macro
Syntax: VBA - procedures :: Sub BSCallOptBoxes(ByVal Switch As Boolean), and Private Sub BoxSequence_Click()
-
WorkSheet elements 1 - shapes
- add a Rectangle and other elements to the drawing layer, apply shape format, and text format. Include a WS hyperlink, Protect Sheet, and apply a password
- Price Volume combo charts
- VBA concepts
- Visual Basic Editor (VBE)
-
VBE interface
Elements of the VBA editor - interface
Includes: Menu bar, Tool bars, and Windows - project explorer, properties window, code window, immediate window, locals window, and watch window
-
Code window elements
Code writing, blinking insertion point (BIP), and margin indicator bar
Includes: Key word and Comment text, the Blinking Insertion Point, and Margin indicator bar: Call return, Execution point, and Break point
-
VBE interface
Elements of the VBA editor - interface
- Variables
- Variables 1
- Numbers - integer to floating Formulas and VBA code to return minimum and maximum values for Integer, Long, LongLong, Single, and Double. All formulas are binary (base 2)
-
Numbers - trailing minus sign [QandA]
A UserForm textbox includes the usual number validation techniques, yet accepts a number with a trailing minus sign. How can this be? Trailing minus numbers in VBA, a demonstration example, and FORMAT specification.
Includes: Numbers with trailing minus sign. Format function with "#,##0.00;#,##0.00-" specification, and WS custom function #,##0.00;[Red]#,##0.00-
Syntax: VBA: IsNumeric, Val
- Variables and constants - scope
- Name 255 character limit
-
Line continuation and its limit
VBA's line-continuation (represented by the <space - underscore - enter> sequence) and the 24 limit. Also includes an example of the Excel 64 nested functions limit
Includes: Concatenation & and +, Application.Rpt, and FormulaR1C1,
- Procedures
-
Function procedures - user defined
an introductory guide to Excel UDFs. 1. No arguments, 2. a fixed number of arguments, 3. fixed arguments with multi cell array, 4. optional arguments, and 5. an indeterminate number of arguments. Writing values to a Range object. Using a For Each...Next loop to read elements of a simple ParamArray
Includes: Application.Volatile, Application.Dialogs(xlDialogFunctionWizard).Show, ActiveCell.FunctionWizard, FormulaArray, IsMissing, Union operator, and Intersection operator, ParamArray -
Sub procedures - syntax
an introductory guide to the sub procedure syntax, and writing and executing sub procedures.
Includes: Syntax, Public and Private declaration, Static declaration, Passing arguments, ByRef and ByVal, and Exit Sub
Syntax: Sub, End Sub, Exit Sub, Empty, IsEmpty, TypeName, VarType -
Calling private procedures
- use the Application.Run method to call private procedures in other modules. Demonstrates private macros and functions.
Includes: Private, Application.Run (arguments by position and by name) -
Functions: formula to vba, and recursive loops [EandA]
- an Exercise with Answer - exercises for 90045 session 8. Finance formula to function, recursive loops, and paramarrays
Includes: For...Next loop with Exit statement
-
Function procedures - user defined
an introductory guide to Excel UDFs. 1. No arguments, 2. a fixed number of arguments, 3. fixed arguments with multi cell array, 4. optional arguments, and 5. an indeterminate number of arguments. Writing values to a Range object. Using a For Each...Next loop to read elements of a simple ParamArray
- Controlling code execution
- If...Then...Else statement with application to a tax payable case example, a review of the Excel IF function, with line-break formula (Alt + A), and relative name addressing
- Select Case statement with application to a tax payable case example (introduced in the If...Then...Else statement module)
-
For...Next statement
application of the VBA For...Next statement to a discount factor example
- Arrays
-
VBA arrays
VBA arrays - static, dynamic, and aspects of dimension. Includes Dim, Redim, Preserve, UBound, and LBound
Syntax: VBA LBOUND, UBOUND -
Array dimensions - number
VBA function GetArrDim - get array dimension. Returns an integer value of the number of dimensions in a VBA array
Syntax: VBA - Err.Number, IsNumeric, On Error, and UBOUND -
Array dimensions - limit
- examines the 60 dimension limit of a VBA array. Determining the number of dimensions of an array
Syntax: VBA - Debug.Print, Err.Number, LBOUND, On Error, vbNewLine, and UBOUND -
TaxTable :: Array and subarrays a1 [QandA]
An exercise with 3-D arrays, extracting dimensions, and an application to income tax. Tax table to WS array. Tax table to VBA array.
Includes: UBound, Select...Case, If..Then...ElseIf, Vlookup, Month, Year, MacroOptions -
Array power 2 [EandA]
- an Exercise with Answer - 1 Write a user defined function (UDF) named ArrPwr2 that takes one argument, a range object, and returns the square (^ 2) of the argument as an array. 2 Write a test procedure, named TestArrPwr2 to pass the Sheet1!B3:C5 array to the ArrPwr2 UDF, and assign the return value to a variable named Ans of type Variant
Includes: Function with Range argument, double For...Next loop, Process Header row and Data row(s), Display InputBox method (various), and Write output to WS -
WS VBA range array comparison b1 [QandA]
- question - what is the difference between a range and an array in both Excel and VBA?
Includes: Excel WS range. range operators; Excel WS array, array arguments, array formula (CSE); VBA range object, declaration, set statement, name and count properties; VBA array, multi-dimension; Mixed WS VBA range array, variant array, array of variants, and 3D worksheet array
-
VBA arrays
VBA arrays - static, dynamic, and aspects of dimension. Includes Dim, Redim, Preserve, UBound, and LBound
- Visual Basic Editor (VBE)
- Application, Workbooks, Worksheets, and Range
- Worksheets
-
Does worksheet name exist [QandA]
from the Q and A series. Shows how to determine if a worksheet name exists in a workbook
Includes: Empty, Goto, OnError, Sheets(Index)
-
xlfValidateSheetName function
Function: xlfValidateSheetName - returns a Boolean FALSE if name contains one or more invalid characters
Includes: VBA: Array, Exit For, InStr, LBound, Len, UBound
-
GetSheetList macro
Macro: GetWSheetList - returns the name and visibility status of all worksheets in the active workbook. GetWSandCSList - return a list of Worksheets and Chartsheets, excluding MacroSheets. Includes XlSheetVisibility, VBA.CInt, an Array with negative index numbers, Worksheet.Name, Worksheet.Visible and Sheet.Type properties, passing arguments to a macro, VBA.TypeName function
Includes: XlSheetVisibility Enumeration - xlSheetVisible, xlSheetHidden, and xlSheetVeryHidden
-
Add worksheets from SheetList
An Exercise and Answer: Add a series of new worksheets from a list. Include a Reset procedure.
Includes: Module constant, DisplayAlerts, ScreenUpdating, Tab.Color, Worksheets.Add After, Buttons.Font.Color, and Buttons.OnAction
-
Sort worksheets from SheetList [QandA]
Question and Answer: How do I sort the worksheets and chartsheets on an index in an Excel range?
Includes: A macro to sort worksheets based on a list in an Excel range. Features: For...Each loop, For...Next loop. Cell in Range object.
-
Sort SheetList and WS macro (4 controls)
xlfSortVisibleWSheetsFromList - sort a list of sheet names using Option Buttons & Button_Click, and sort WS based on SheetList order. Interface with on-sheet Form controls. GroupBox, OptionButtons, and Button. Uses Link Cell to VBA code.
Includes: enumerations - xlAscending, xlDescending, xlNo; Sort Key1, Order1, and Header. Application.ScreenUpdating, and Sheets.Move br> -
Sort SheetList and WS macro - ver 2 (3 controls)
xlfSortVisibleWSheetsFromList - sort a list of sheet names using Option Buttons, and sort WS based on SheetList order. Interface with on-sheet Form controls. GroupBox, OptionButtons, and Button. Uses Link Cell to VBA code.
Includes: enumerations - xlAscending, xlDescending, xlNo; Sort Key1, Order1, and Header. Application.ScreenUpdating, and Sheets.Move br>
-
Does worksheet name exist [QandA]
from the Q and A series. Shows how to determine if a worksheet name exists in a workbook
- Range
-
Range object (1)
- part 1 - an overview of the Application to Range object hierarchy. Range object examples include single cell, and multi cell;
Includes:Loop through range elements with the .Item property. Assign values to an array of type Variant, and read with the IsEmpty function
Syntax: .Cells, .Item, and Offset
-
Range object (2)
- part 2 - range object examples include single cell, and multi cell; writing the range to a VBA array - For...Next loop with dimension counter, For...Next, and For Each...Next loops. Write the return value arrays to the Immediate Window. Pass arguments to the Write sub procedure. Bonus code - how to clear the Immediate Window with SendKeys.
Includes: Cells, Column, Count, Left, LBound, Offset, Range, ReDim, Resize, SendKeys, UBound, vbNewLine, WorksheetFunction
-
Copy paste - example
this module reviews Worksheet copy and paste, then illustrates VBA code for Copy method and PasteSpecial method, Copy with Destination, and Value assignment
Includes: ClearContents, Copy(Destination), Offset, Paste, PasteSpecial(Paste, Operation, Resize, SkipBlanks, Transpose), Range(n).Range("A1"), Set, ScreenUpdating
Syntax: PasteSpecial method, and Copy(Destination) method
-
Excel range - address of first and last cell
this module use of selected functions from Excel's lookup and reference function category, and CELL function from the information category
Includes: examples that return the address of the first cell, last cell, and address of range. Value of first cell and last cell.
Syntax: ADDRESS, CELL, COLUMN, COLUMNS, INDEX, INDIRECT, ROW and ROWS
-
WS VBA range array comparison b2 [QandA]
- question - what is the difference between a range and an array in both Excel and VBA?
Includes: Excel WS range. range operators; Excel WS array, array arguments, array formula (CSE); VBA range object, declaration, set statement, name and count properties; VBA array, multi-dimension; Mixed WS VBA range array, variant array, array of variants, and 3D worksheet array
-
Range object (1)
- part 1 - an overview of the Application to Range object hierarchy. Range object examples include single cell, and multi cell;
- Worksheets
XLF Part III :: Spreadsheet forms and controls [TOP ] [Toggle]
- UserForm - Concepts and Applications
-
Initialize event - example
this module contains VBA code to initialize UserForm objects. RefEdit, and the TextBox object with DropButtonStyle
Includes: DropButtonStyle, fmDropButtonStyleReduce, fmShowDropButtonWhenAlway, ShowDropButtonWhen
-
ActiveX controls - toggle button
VBA Macros - Full Screen, and Clear Screen.
Includes: Control properties: Caption, Enabled. Application properties: Caption, DisplayFormulaBar, DisplayStatusBar, ExecuteExcel4Macro. ActiveWindow properties: Caption, DisplayGridlines, DisplayHeadings, DisplayHorizontalScrollBar, DisplayVerticalScrollBar, and DisplayWorkbookTabs
-
NPV engine
this module develops code for the NetPV engine, with VBA NPV, WS NPV and NPV from first principles.
Includes: Option Private Module, # and % TDC's, assignment of range values to VBA arrays, and passing arguments to sub routines
-
UserForm alternatives (1) - ListBox and ComboBox
- a module that demonstrates the use of UserForm alternatives - Data Validation from List, on sheet ListBoxes and ComboBoxes - no VBA
-
Initialize event - example
this module contains VBA code to initialize UserForm objects. RefEdit, and the TextBox object with DropButtonStyle
- Boxes
-
Box cancel button coding
Box Cancel button Coding: for a Message Box (MsgBox), and an Input box (Application.Inputbox method)
Includes: MsgBox, Application.InputBox, and Evaluate
-
MsgBox icons
A quick guide to VBA MsgBox() function icons and a comparison to Data Validation Alert icons
Includes: , VbMsgBoxStyle enumerations, MsgBox enumerated constants, Data Validation Error Alert Style icons, XlDVAlertStyle enumerations. VBA code for MsgBox function, and Validation Error Alert
-
InputBox default - type 8
Describes the InputBox method Default value - as a Range Name, property and string, and - as a Range Address, address and name
Includes: VBA Quick Info, Name Manager, WB scope Constant, InputBox method Default parameter, Locals Window,
Syntax: Name.Name, Range.Address, and Range.Name properties -
Built in dialog boxes
Built in dialog boxes. Examples - Print, Save As, and Workspace. )
Includes: Show method, Dialogs property, and XlBuiltInDialog collection
-
Box cancel button coding
Box Cancel button Coding: for a Message Box (MsgBox), and an Input box (Application.Inputbox method)
- Interface - GUI, Selectors, and Ribbon
-
Ribbon XML - an introduction
- add a new group to an existing Excel ribbon tab, write the XML code, and VBA Callbacks
Includes: Ribbon customization with XML :: tab, group, button, insertaftermso, supertip, imagemso, onaction. Demonstrates development of the customUI file
-
Multiplication program - example
What is a UserForm? Adding a UserForm to a project. Steps to creating a UserForm
Includes: frmMultiply Form Module, Controls, Code Module, Change event, Click event, Multiplication engine, Show method with vbModeless
-
Multiplication program - KeyPress version
frmMultiplyKP1 features the TextBox.KeyPress event. Filter 0 to 9 and decimal separator. Addition filter version for
minus sign including trailing
Includes: TextBox KeyPress event, Select Case Ascii. TextBox.SelStart property. TextBox Change event
-
Clock and Time interface
Clock macros: add clocks to the Application.Caption, WS.Name, in cell, on sheet TextBoxes, and a progress bar indicator version
Includes: Application.Caption, Application.OnTime, a two procedure loop, VBA.Format, Shapes.TextFrame.Characters.Text property, ASCII, Unicode, and Alpha. WorksheetFunction.Rept, Application.Run. Open and Before_Close events
Syntax: Application.OnTime, and Application.Wait. VBA (with examples): Date, DateValue, Hour, Minute, Now, Second, Time, Timer, and TimeValue. VBA (with examples): ChrW, and String.
-
Calculator program - example
- an extension of the Multiplication program. Includes Division, and an Output feature with Range select.
Includes: Form Initialize, BackColor, SpinButton, DropButtonStyle, ShowDropButtonWhen, TextBox KeyPress event, Select Case Ascii, Enabled
-
Ribbon XML - an introduction
- add a new group to an existing Excel ribbon tab, write the XML code, and VBA Callbacks
- Excel
-
What version of Excel am I using?
- use the Excel File tab to discover the version of Excel you are using
-
Get Excel version macro
- GetXLVer - get Excel version VBA macro. Returns a message box displaying the Excel version details.
Syntax: Application BUILD, VERSION, OPERATINGSYSTEM properties, and ENVIRON function
-
GetFT formula
- a custom formula that returns the formula of the given reference as text. Incorporates a dynamic relative address of the reference cell.
Syntax: WS functions: ADDRESS, FORMULATEXT, and ISFORMULA
-
GetCF function
- a custom function. Returns the formula of the given reference as text
Includes: VBA Address, Formula, IsMissing, ReferenceStyle, Text, Value. On Error Goto
Syntax: WS functions: ADDRESS, FORMULATEXT, and ISFORMULA
-
GetWS2UFcolor function
- a custom function. Returns the Fill color of a WS cell in UF &H...hex...& 11 character format
Includes: Interior.Color property
-
Excel functions keys
- function keys F1 to F12 with description.
-
Set Excel window dimension to 720p [QandA]
- demonstrates an easy way of setting the Excel window to 720P resolution for uploading screen capture to sites such as YouTube
Includes: Application, WindowState, Top, Left, Height and Width properties. Pixels to Points conversion
Syntax: Application Height and Width properties -
Floating point precision, and cell formats [QandA]
- Why does the logical expression formula =0.1=(1.2-1.1) return FALSE?
Includes: Using Evaluate Formula to display the double precision value, and Using F2 and F9 to display the double precision value. With floating point examples
-
Fix Excel's Ctrl+Shift+0 keyboard shortcut
- If your WS Ctrl+Shift+0 keyboard shortcut won't work, then follow these steps to fix the problem.
Includes: Ctrl + Shift + 0, Advanced Key Settings, Switch Keyboard Layout
-
What version of Excel am I using?
- use the Excel File tab to discover the version of Excel you are using
- VBA
- Opening macro enabled workbooks - follow these steps if a Security Warning appears when you open the session workbook. Security alert, enable content
- Require user to enable macros - present a Warning Message screen requiring the user to enable macros to continue using the workbook
-
Versions - VBA
- VBA versions - Excel 2003 to 2013. Applies the conditional #If...Then...#Else directive to a description of the GetCF function
Includes: #If...Then...#Else compiler directive -
Colours - VBA
- RGB colour circles and additive mixing, the ColorIndex property, RGB and decimal colour values, and the Color property.
Includes: Color additive mixing. 56 colours of the ColorIndex. RGB to positive decimal, decimal to RGB, and RGB to negative decimal. Binary And, and the backslash operator -
Binary addition: xlfBinAdd1 function
- a custom function xlfBinAdd1 returns a string value from the addition of two binary numbers (sum and carry method)
Includes: rules of binary addition, WS: Max, and Substitute VBA: CVErr, Len, Mid, and xlErrValue
- Windows
-
Windows voice
- text-to-speech is available in Excel with the VBA Speech.Speak method. This module shows how to change the voice in the Windows Time and Language settings
Includes: Application.Speech.Speak method. Passing arguments to a sub procedure - Windows region settings - demonstrates changing the Windows > Region Settings environment. The impact on Excel's currency, time, and date is illustrated. The rule of 2029 is discussed
-
Windows voice
- text-to-speech is available in Excel with the VBA Speech.Speak method. This module shows how to change the voice in the Windows Time and Language settings
XLF Part IV :: Applications in finance [TOP ] [Toggle]
- Option pricing - Black Scholes model
-
Black Scholes Excel and VBA
- European option on a non dividend paying stock - using Excel cell formulas (d1, d2, Nd1, Nd2, Call and Put), VBA UDF function code and testing procedure
Includes: WS Norm.S.Dist, VBA Format, Norm_S_Dist, TypeName, and optional arguments
-
Black Scholes on the HP 10bII+
- European option on a non dividend paying stock - using Hewlett Packard calculator - 5 steps (d1, d2, Nd1, Nd2, Call and Put)
Includes: HP 10bII+ keystrokes
-
Black Scholes in Google Sheets and GAS
- the BS model written in JavaScript. JavaScript functions for cumulative normal distribution: xlfBSCall, xlf NormSDist, and xlfBSPut. Application of the Abramowitz (1972) equations 26.2.1 for the N(x) approximation
Includes: JavaScript Math.exp, Math.log, Math.pow, Math.sqrt. Approximation to the cumulative normal distribution function N(x) from Abramowitz (1972) equations 26.2.1 p931, 26.2.17 p932; and Bellalah (2008, Appendix 6, p126), constants a = 0.2316419; a1 = 0.31938153; a2 = -0.356563782; a3 = 1.781477937; a4 = -1.821255978; a5 = 1.330274429;
-
Black Scholes coded with Python and xlWings
- as an Excel UDF coded with Python and available via xlWings. Includes Python code, VBA modules and a quick guide to Python, xlWings and installation in an Excel workbook
Includes: Black-Scholes Python, Black-Scholes xlwings, numpy, scipy, pip. Quick guide to installation of Python and Anaconda (with comparison), Python editors, and code for BSOptionPY
-
Black Scholes - implied volatility and Goal Seek
- from the Q and A series. Shows how to estimate implied volatility across multiple options using Goal Seek without selecting individual instruments
Includes: Range.GoalSeek method with syntax. Array range objects, arrays of numbers, and looping through array elements
-
Black Scholes Excel and VBA
- European option on a non dividend paying stock - using Excel cell formulas (d1, d2, Nd1, Nd2, Call and Put), VBA UDF function code and testing procedure
- Simulation
-
Triangle numbers [EandA]
- an Exercise with Answer - return the triangle sum of the arithmetic sequence 1 + 2 + 3 + ... + (n -1) + n
Includes: x-y charts of the triangle, with VBA code to create the triangle series points. Finance aspects - the number of elements in the portfolio variance matrix
-
Function arguments and random number series [QandA]
- why does my Monte Carlo based option pricing function show two different values in the function arguments dialog box?
Includes: Geometric Brownian motion, Norm.S.Inv and Rnd. Random number generator (RNG) and the VBA Randomize function
-
Triangle numbers [EandA]
- an Exercise with Answer - return the triangle sum of the arithmetic sequence 1 + 2 + 3 + ... + (n -1) + n
- Data analysis and transformation
-
Data variance-covariance engine [QandA]
- our team is trying to develop a VBA version of the ATP covariance dialog box. Where do we start?
Stage 1 in the development of a VBA version of the ATP variance-covariance (VCV) dialog box. Includes code for the VCV calculation engine
Includes: the interface links, headers rows, data, calculation and storage of the covariance matrix, and writing the VCV to a WS range. With test data from the AGK, CSL, SPN, and SKT data set. Application.Transpose, Application.Index, Offset, Resize, UBound, and module level declarations for the Interface Link
-
Distribution :: Normal PDF - GAS and VBA
A quick guide to normal distribution and its probability density function (PDF). The PDF formula is coded in JavaScript, and VBA. Includes the standard normal distribution and z value.
Includes: normal distribution, probability density function, NORM.DIST, NORM.S.DIST. JS :: Math.exp, Math.PI, Math.Sqrt
-
TaxTable :: Array and subarrays a2 [QandA]
An exercise with 3-D arrays, extracting dimensions, and an application to income tax. Tax table to WS array. Tax table to VBA array.
Includes: UBound, Select...Case, If..Then...ElseIf, Vlookup, Month, Year, MacroOptions
-
Data variance-covariance engine [QandA]
- our team is trying to develop a VBA version of the ATP covariance dialog box. Where do we start?
- Project seed modules
-
Write a WS array to named array constant [Seed]
- a Quick HowTo: use VBA, to write an Excel numeric array to a named array constant. Sample VBA code included
Includes: xlArr2ArrC macro to write the Excel range inArray to a named constant ArrCon
-
Write a UDF to convert an ASX trading date to a YAHOO data date [EandA] [Seed]
- an Exercise and Answer - write a Public (UDF) Function procedure named YDate that returns the YAHOO date, adjusted for seasonal time adjustments, corresponding to the ASX trading day date.
Includes: YDate function with optional arguments passed from an array or array constant. Demonstrates the VBA Split function, Variant arrays, Arrays of Variants, and the VBA CDate function
-
Write a WS array to named array constant [Seed]
- a Quick HowTo: use VBA, to write an Excel numeric array to a named array constant. Sample VBA code included
- xlf :: eXceL at Finance :: excelatfinance.com
- Maintained by: Dr Ian O'Connor, CPA. - Victoria, Australia.
- Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)] ::: lastmod
2023-02-25