By using vba.booksticle.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

949 Steps to 6 Figures by Learning Excel-VBA and Other Skills


Table of Contents


Introduction

Chapter 1-Learning Techniques
1.Learn the Terms
2.Acronyms
3.Learn via Immediate Window
4.Learn via Macro recorder
5.Learning by stepping through code🔝
6.Learn to Juggle
7.Self Learning
8.Learn via sample code
9.do one then do many
10.Small to big, one piece at a time
11.Learn by going from small to big
12.Download website code
13.List of terms to know
14.You Don't Have to Understand Everything
15.Many ways to skin a cat

Chapter 2-Basic Concepts
16.Copy
17.Hidden
18.Visible - basic
19.Default
20.Syntax
21.Active
22.Rows - basic
23.VBA is a programming language, syntax
24.Constants
25.vbCRLF
26.vbCR
27.vbNullChar
28.vbTab

Chapter 3-Excel Knowledge
29.Excel Knowledge Overview
30. Basic Excel Functions to Know
31.Assignment stmt
32.Columns(1).select - selects a column
33.Selection.EntireColumn.Hidden = True ' recorded
34.Activecell.Column
35.Activecell.Row
36.Rows, Columns and Cells
37.Selection.Insert Shift:=xlToRight - recorded
38.Columns("A:P").clear
39.Insert a column - Columns(lCol).EntireColumn.Insert ' recorded

Chapter 4-Cells
40.cells - basic
41.Range vs. Cells
42.Range("A1:" & Cells(r,c).Address).Copy
43.ranges - "A1" "A1:C4" range_name
44.Cells.Rows.Count
45.Value vs. formula
46.Rows("1:1").select 'a row
47.Rows collection
48.Selection.Address - vb property - returns the $A$1 formatted address of the selected cell(s)

Chapter 5-Sheets
49.xl vs. VBA ranges
50.Sheets aka WorkSheets
51.sheets vs. worksheets collection?
52.Collections
53.Sheets Collection
54.ActiveSheet.name
55.CreateSheet "name" - adds a new sheet and deletes current one??? udf
56.ActiveWindow.SelectedSheets.Delete - recorded to delete multiple sheets (displayalerts=false) to turn off warning
57.Create sheets from a list

Chapter 6-Workbooks
58.Workbooks Collection
59.ActiveWorkbook.SaveAs sFile
60.ActiveWorkbook.Save
61.SaveCopyAs
62.ActiveWorkbook.SaveCopyAs sFile
63.Workbooks.Open
64.Workbook_open - event
65.Using Multiple Workbooks
66.WorkBooks.Open sFileout, UpdateLinks:=False
67.set wb = ActiveWorkbook
68.Workbooks(sWB).Close False
69.Windows(sWB).Activate
70.Setting Sheet
71.Sheets(2).visible = True
72.Sheets.count
73.Sheet names have size and character limitations
74.ActiveSheet.Copy After:=Workbooks(sMainWBK).Sheets(Sheets.count)
75.Create sheets from a list
76.Delete a Sheet - turn off displayalerts before delete (sh.delete)
77.ActiveWorkbook after opening an excel file
78.WorkBook
79.ActiveWorkbook.Close False
80.Activeworkbook.name
81.ThisWorkbook
82.Workbooks Collection
83.ThisWorkbook.Sheets(....).Range(....) ' use when a different sheet is active
84.VBA on 2 or more Excel files opened at the same time

Chapter 7-Getting Started with Macros
85.Developer Ribbon
86.Switching between xl and VBA and back
87.What you can record and what you can't (loops, decisions, flexibility)
88.Hello World!
89.Code indenting
90._ - continuation character
91.VB or Visual Basic Editor
92.The 4 Main VBE Windows and the Rest
93.autocomplete
94.Create syntax error to save a location you want to go back to
95.Colors in vb Editor - Green - comment - blue Excel statement ; red = syntax error
96.Ctrl Shift F2 - Go back to previous routine in VB editor
97.Recording a Macro and Generalizing it
98.Introduction to the Macro Recorder
99.Macro Security
100.Learn by using the Macro recorder
101.Finding the Macro being Recorded
102.Macro recorder hardcode removal
103.Activecell.formulaR1C1 = .... - code from macro recorder
104.Activecell.FormulatR1C1
105.Debugging with Msgbox's
106.Using the Macro Recorder
107.Debugger
108.Holding the shift key when excel starts stops macros from running
109.Understanding someone else's vba code🔝

Chapter 8-String Processing
110.Strings, Numbers and Variables
111.Option Explicit
112.as String
113.Public or Global variables
114.as Integer
115.Scope of variables
116.Private scope
117.Module variable scope
118.Static variable
119.Naming convention
120.Strings
121.Left(sting,len)
122.Right(string,n) - string function
123.Mid - Extract data from a string
124.Mid(string,start[,end]) - string function
125.Dim lStart as Long, lGo as Long, ..
126.Dim sFile$, sZipfile, test as string
127.Dim SpecialPath As String
128.instr(lcase(..) )
129.instr( ) - string function
130.Trim - function string removed leading a trailing characters
131.LTrim - VBA func
132.Space$(512)
133.Selection.Replace what:="#N/A", Replacement:="0", LookAt... 'Recorded
134.Replace
135.& - Concatenation
136." - "" - using quotes in a string
137.Concatenate a string to create a range
138.range(cl(c+1) & fr & ":" & cl(c+1) & r).select
139.Len(string) - vba function
140.Asc( ) - VBA string func converts s to a number
141.ActiveCell.Address
142.RemoveLastComma
143.Cells.Clear - Clear all cells
144.Regular Expressions (see Save as PDF)
145.lcase
146.RTrim - VBA func
147.Ucase
148.Replace

Chapter 9-Excel Functions
149.=subtotal xl func
150.=vlookup (xl func)
151.=if
152.=ifna (xl func)
153.=search
154.Return a value from a function
155.Function returning a value
156.Return multiple values from a function by returning a comma separated sting and parsing the results
157.Using fx to understand a formula
158.=match xl

Chapter 10-Cells
159.Activecell.formulaR1C1 =
160.Cells(i,j).Text
161.Activecell.formula

Chapter 11-VBA Statements
162.Variables
163.Global variables and variable scope
164.Constants - Const SW_SHOW = 1
165.Load cell value to a vba variable
166.Function -
167.nested functions replace(replace(sURL,
168.Select Case - vba stmt
169.GoTo
170.gotolabel:
171.Gosub routine_name
172.=
173.<> - not equal, condition
174.As Worksheet
175.Call routine_name ' call is optional but clearer to new users
176.[=subtotal(3,A:A)-1]

Chapter 12-Selecting
177.Range(selection, selection.End(xlDown)).Select - select area - recorded
178.Selection.End(xlUp).select ' recorded
179.Sheets("name").select - selects a sheet
180.ActiveCell.Offset(1,0).select
181.AddBorders
182.Quit - Application.Quit
183.SelectPartialSheetName - I wouldn't know the whole name of a sheet but knew part of it's name
184.Select a column - Columns(i).select

Chapter 13-Formatting
185.Formatting Routines
186.Selection.NumberFormat = "mmm-yy"
187.Selection.Numberformat = "#,##0.0,,,"
188.Columns.("A:E").EntireColumn.AutoFit
189.Rows("9:34").EntireRow.AutoFit
190.format
191.ActiveWindow.FreezePanes = True/False
192.format(lVersion,"000")
193.activecell.MergeArea
194.MergeCells
195.Selection.Merge
196.FixXYZFormat - the format of data needed to be fixed to a format that the code could process
197.Format dates
198.Footer
199.HighlightTotalRow
200.Selection.NumberFormat = "m/d/yyyy"
201.Seletction.Style = "Percent" ' recorded
202.displaycolumns - udf
203.Columns("A:A").ColumnWidth = 7.43 - recorded
204.Collapse/Expand outline
205.ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ' recorded
206.Autofit
207.Borders - Record the code
208.formatting routines - almost all are recorded and modified
209.Rows("1:1").RowHeight = 52.5 ' recorded
210.Columns("H:I").NumberFormat = "#,##0"
211.Selection.Font.Underline = xlUnderlinestyleSingle
212.Outline/Group/Ungroup data
213.s,Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ::-""??_);_(@_)" 'recorded (g)
214.Selection.WrapText = False ' recorded
215.Formatcols - Format the excel while recording
216.FormatXyz - udf - to format data usually via recorded code,

Chapter 14-Filtering with VBA
217.WorksheetFunction.Min( ) I use application.evaluate
218.Clear Filters Without Removing Them
219.GetNextRow - filtering udf
220.ActiveWorkbook.Worksheets("....").Autofilter.sort.sortfileds.clear ' clears a filters sorting ' recorded
221.Selection.autofilter = turns off filter
222.Hidden property
223.Subscript out of range
224.Cells.autofilter field=lCol, Criteria1:="=*CCRT*", Operate:=xlOr, Criteria2:="IBOR*"
225.Cells.autofilter field=lCol, Criteria1:="=*IBM*", Operate:=xlOr, Criteria2:="Sales*"
226.Remove filtered data but keep filters
227.Public gsFilters() As Filter_fields 'Filter fields is a udt
228.AutoFilterMode
229.Filter Problems copying moving...
230.range(...).Autofilter Field:=1, criteria1:="=" & sValue, Operator:=xlAnd = recorded sets a filter
231.Filtering and visible rows
232.Rows(i).hidden

Chapter 15-Looping or Repeating Statements
233.Loop
234.Do While - Loop
235.for k = 1 to 12 step 3 ' loop
236.for z = len(sSQL) to 1 step -1
237.exit for ' prematurely exit from a for loop
238.GoTo next_for
239.next_one:
240.For Loop
241.Do Until n = 0
242.While Not EOF(1) ... Wend - loop
243.Exit Do
244.Do - Loop ' no while needs Exit Do
245.Exiting loops
246.nested for loops
247.Goto Loop
248.For each (loop) item in collection
249.Next
250.Doevents '

Chapter 16-Data Types
251.as Variant
252.As Long
253.as Single
254.as Range
255.As Object
256.As Boolean ... True/False
257.As Byte
258.As Excel.Range
259.Type fiilter_Fields row as Long column as Long End Type

Chapter 17-Commonly Used Routines
260.RunProgram "http.." (File Associations need to set up)
261.Findlastrow - udf - finds the last row in a column
262.Findlastrow - udf - finds the last row in a column
263.FindDataCol("fieldname",row_nbr) udf
264.ColumnLetter - udf - converts a column # to a column letter
265.FindDataRow("string",col_nbr)
266.FindDataCol - udf - 🔝
267.FindLastCol(1) - find the last column passed a row udf

Chapter 18-Projects
268.Convert a Name from Last, First to First Last
269.Open Websites and Files
270. Add a Table of Contents or Summary Sheet
271.AutoSort
272.Create a Sheet with a number of columns to run queries and process Excel output
273.Birthday Problem

Chapter 19-Excel Objects, Properties, Methods, Collections and Events
274.Properties
275.Events
276.Workbook_close - event
277.Methods
278.Default property
279.Workbooks(thisworkbook.name).sheets("xxxx").Range("DER_cc") ' get the range name in another workbook
280.Workbooks(sWBK).Sheets("CPR_LIst").Range("portfolio")
281.beforeSave
282..Select
283.Selection. (formula, copy)
284.ActiveCell.Offset(1,0).Address
285.Selection.Rows.Count
286.ExcelInstances
287.Is Nothing - Objects?
288.Objects

Chapter 20-The Clipboard
289.Clipboard routines - Set and copy to clipboard (plan2020 has this)
290.Paste
291.range("range_name").copy
292.Relative vs. Absolute Address $A$1 vs. A$1 vs. $A1 vs. A1 -
293.Clipboard routines
294.Selection.Pastespecial Paste:=xlPasteValues... 'recorded
295.Pastespecial pasteformats
296.Activesheet.paste
297.Selection.cut - Clipboard
298.Application.CutCopyMode = False - gets rid of crawling ants
299.Sheets(Array("sheet1","sheet2").copy
300.Clipboard - basic
301.Copy Transpose
302..Copy
303.Selection.copy
304.Selection.Copy - copies the active selection to the clipboard
305.Selection.PasteSpecial ... ' recorded
306.ClearClipboard - udf - Report Engine
307.selection.PasteSpecial Paste:=xlPasteColumnWidths, ..' recorded

Chapter 21-Dates and Time
308.Now() - returns the date time
309.IsDate(string_field)
310.GTODList(sStype, aOffsets) - date Get Type of Date list format "3,6,12,15" a list of prev offsets
311.LBDOM - dates - udf - Last Business Date of the Month
312.Month(vdate)
313.Year(vDate)
314.Second(now())
315.DateValue("1/2/2020")
316.End of Month issue - two system used different month-end dates problem
317.GTODList(type, string of offesets - ex. "0,1,2,3,6,9,12"
318.Timezones
319.Last_day_of_prev_month
320.Last_day_of_month
321.fdom - first day of month -date udf
322.CVDate(string_field)
323.DateAdd("s",Seconds, Now)
324.Date = #1/4/2021#
325.IsBizDay(vDate)
326.Date formatting yyyy-mmm-dd
327.IsDate(string_field)
328.NBizDays(startDate,n)
329.If Day(Now()) <= 19 then
330.Weekday - dates
331.GTOD - GetTypeOfDate - e e-system C c-system
332.DateAdd("m", -1, vDate))
333.Folders

Chapter 22-Files
334.ChDir - Files
335.Get File Name Example
336.ActiveWorkbook.FullName
337.FileDateTime(sFileName)
338.All About Files
339.Copy Files
340.Creating a folder
341.ReadOnly
342.Mkdir - files - creates a new directory
343.Dir - files - process a directory or check if a file exists
344.OpenDQFile - udf to open the Data Quality file from web/sharepoint and unzip it
345.Open iniFile For Input As 1
346.SaveStringtoFile string, "c:\xyz.txt" - saves a string to a file
347.SetAttr
348.bOpenSeqFile(sFile,"A") ' "O" output "I" input
349.Open .. for binary access
350.GetFileName(fullpath) - returns the file name without directory
351.Do While Not EOF(x)
352.LoadFileToString(sFile) - udf
353.FileLen(sFile) - gets the length of the file
354.FileCopy from, to
355.Use file name to denote something like "-quick" isn't the full result
356.View a directory ' runprogram on the Sharepoint directory name
357.Freefile
358.Scripting.FileSystemObject"
359.FilesSystemObject.CopyFile sfrom, sTo
360.Dim FileSystemObject As New FileSystemObject
361.Print #x,string
362.set fso = CreateObject("scripting.filesystemobject")
363.\\ ' network file prefix
364.GetVersionForECMTemplates - goes thru a directory and get the last mod and size of each file
365.Err = 53 ' file not found
366.GetLastSlash(s) - File
367.Show directory via File Explorer - RunProgram "C:\directory_name
368.FileDateTime(sFileName)
369.UnzipAFile(file,directory)
370.udf - CreateMultipleDirectorys from a list (files)
371.Walk a file - get all files in a directory
372.CreateDir
373.AddFinalSlash - udf - files
374.XML File format
375.txt files
376.Line Input #x, sData
377.Close #x ' Close a seq file
378.LoadData(sFile) - udf - loads a file's data to a string
379.vFileLen(sFile) ' udf to create an excel function to get the filelength
380.kill - delete a file
381.How to write a function to open a zip file when the file sometimes is zip and sometimes xlsb
382.\ as a folder separator
383.SeqIO.bas
384.dir(filename) = ""
385.File wildcard character * or ?
386.Error File in use
387.a filename can't use certain characters line "/" so change it if creating it from data
388.elseif err = 75 then ' directory already exists?
389.tab delimited file
390.SaveStringToFile - udf 🔝
391.File suffix - zip, xlsb, txt, xls, xlsx, xlsm, pdf, htm, html
392.GetFileCreator - internet - used shell to get the name

Chapter 23-Macros to Record
393.Convert to Values via Paste Special
394.Application.Autocorrect ' record

Chapter 24-API - Application Programming Interface
395.Declaring APIs
396.Ptrsafe
397.API - Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA(ByVal hWnd As Long, ...
398.32 bit vs. 64 bit
399.API - Public Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
400.Sleep - api
401.#If Win64 Then Declare PtrSafe .(old 32 bit declare) #else Declare ...(old 32 bit declare) #end if

Chapter 25-Excel via VBA
402.application.evaluate(sLookup) - runs a vlookup or match or other excel statemnt
403.WorksheetFunction.Min
404.Delete Cells, Rows or Columns
405.=match xl
406.Selection.Delete Shift:=xlUp ' recorded

Chapter 26-Data Analysis and Verification
407.Data_validation(sParms) - used for autorun receive request and run a macro
408.Select a cell - Range(...).select
409.align_ok_key(lKeyCol1 as Long, range1, lkeyCol2 as long, range2) - udf - 🔝 Aligns data from 2 sources
410.CompareTwoFiles() - udf
411.GetStats - udf

Chapter 27-Database and SQL
412.SQL
413.If gADO is Nothing then
414.Connection Sting
415.GetConnection - udf - db
416.Set gADO = GetConnection(GetDatasource())
417.Set gADO = GetConnection(GetDatasource())
418.SQL insert
419.Recordsets
420.vntResult = rs(0)
421.Set rs = nothing
422.rs.Close
423.rs.fields(i).name
424.for i = 0 to rs.Fields.Count - 1
425.Workspace.OpenConnection
426.public gADO As ADODB.Connection
427.GetDataSource - udf - db
428.check_include - udf db implements sql include logic
429.ODBC Dialog
430.As DAO.Recordset
431.QF - db -udf - Query Fix to replace an odd # of quotes with even number to stop sql injection attack (security)
432.CheckForUnadjustedSQL(sSQL, sNote) - udf db looked at SQL and searched for a table that shouldn't be used
433.SQL drop
434.check_include(sSQL,"") - udf db
435.WildCardSelect(...) - db - udf - adds fields to select query based on wildcards - cool
436.LoadListBox(c as object, sSQL as String) - db - udf -
437.RetrieveDataToSheet - udf - db
438.If cn.State = adStateOpen then ' db
439.SQL update
440.Run SQL and save output to a sequential file
441.Dim rs as New ADODB.Recordset
442.SQL delete
443.WhatDB() - udf - queries the gADO.Properties(9) and if an error occurs returns "Not Connected"
444.ODBC
445.RealGo - udf db - detmine if a go in sql is the real one
446.ProcessSQL(sSQL, sSheet, optional sProcess
447.UseProductionDB vs. UseTestDB - udf - that set a global
448.CreateQueryTable - db -
449.SQL insert rows from data
450.LogSQLError -
451.RemoveSQLComments - udf
452.runquery2 - udf - runs a query that returns 2 values
453.GetSystemVar - udf - db - Retrieves a System Variable from a table
454.SQLStuff.bas
455.GetRecodset (sSQL, Optional maxRecords) as ADODB.Recordset '
456.SQL Create
457.SECDataAvailable - udf - db - checks for a condition (cob date from db > month-end data) and send email, generates a file
458.Run a query sample
459.RunSQLFromFile - udf - db 🔝
460.SQL truncate
461.Normalized Forms - db
462.InitDB - udf to initialize a database
463.OpenRecordset
464.If rs.EOF then
465.rs.MoveNext
466.RunQuery( ) - udf db - runs a query that returns 1 value and not a recordset
467.RunSQLFromFile - udf - db 🔝
468.ProcessSQL - udf - very important 🔝
469.RunTheQuery - udf - db
470.AddSemicolon - db - udf - adds a semicolon to the end of a string if one doesn't exist or to beginning as it's a comment
471.Using Database Views
472.if Error = "[Microsoft] [ODBC Driver Manager] Data source..." Then after set gADO and ask to install driver
473.Load_Collateral_values_toDB
474.Embed a query in a function or sub
475.GetDataSource - udf - db
476.LoadQueryToString - udf - db
477.SQL - Check return from query
478.Insert_to_dataissues_click - Loop thru data on sheet and insert it to a table
479.AddFieldToDB (sfield, sTable, stype)
480.LoadSQLtoSheet
481.if rs.fields.count = 1 then
482.reference value in an array aSuffixes(i)
483.Project: SQL UI

Chapter 28-Arrays
484.LoadArray
485.LoadArray(workbookname, Sheetname, header) 🔝
486.For loop - lbound ubound
487.Redim Preserve a(r -2)
488.Array -start with "a", array function
489.Dim a() as string
490.Run SQL from a file
491.DropFieldFromdb - udf
492.Split(string,"value") - converts string into an array
493.for i = Lbound(a) to UBound(a)
494.Array(string, string, ....) - Converts a list of strings to an array
495.Ubound(array) - finds the upper bound of an array
496.Option Base 1
497.InArray(array, the_value)
498.IsArrayEmpty - udf Report Engine
499.Large Arrays - Interview Question
500.Arrays vs. Collections
501.InArray(array,value) - determines if value is in the array - udf
502.Erase ... vba erase (g)

Chapter 29-Batch Jobs
503.auto_open
504.Run "Thisworkbook.WorkBook_Open"
505.Do While (now < vStopTime) and gbStop = False
506.Application.IgnoreRemoteRequests
507.Application.Ontime Dateadd("n",30, Now(),"Sub_to_run_name" ' i
508.Windows Job Scheduler
509.GenerateAllExcelAndDecks - batch - run multiple SQL's and Creates multiple PPT decks
510.StatusBar
511.GetCommandLine() - udf
512.Blink Excel Caption - batch
513.CheckDatabase - udf
514.Application.AskToUpdateLinks = false
515.IsDataAvailable
516.Delay 2 = udf
517.GetParameters - udf - Report engine
518.Logautorun - udf
519.auto_open - udf
520.Wait until a file appears
521.IsAutorun() - udf looks at the filename if has the name autorun then set flag to true

Chapter 30-Web/Internet
522.Hyperlinks
523.HTML
524.Set IE = New InternetExplorer
525.DownloadFileFromWeb(strURL, savetoPath) - udf using api
526.<table>
527.<th>
528.<tr>
529.<td>
530.Application Object
531.AddLinks - convert Jira into links
532.WebQuery - internet - udf - run a url and loads the output to a worksheet - recorded
533.Retrieving Data from a Webpage using Sendkeys
534.DownLoadFileFromWeb(to,from) - udf
535.GetAnchorFromCell - udf - web
536.URLEncode
537.%20
538.Parts of a URL
539.Web Editor
540.Create HTML files
541.udf - Internet - GetHTML2 - good example of using DOM
542.<col width
543.ColumnWidthToPixels - internet code
544.<style>
545.Set IE = New InternetExplorer
546.ftp
547.GetHTML - code to get the source from a web page (doesn't work on pages that use ajax)
548.http, https.
549.Download Web pages

Chapter 31-Solving Problems
550.errors - If it's broken you made a mistake (attitude)
551.#REF error
552.#NAME error
553.#Value
554.#N/A
555.#DIV/0
556.######## - doesn't fit or negative date
557.Misspelling error
558.Error Handling
559.Syntax errors
560.On Error Resume next
561.byRef argument type mismatch - syntax error
562.Error(x) ' displays error message
563.Infinite loops
564.IsError( ) - vba function
565.IsNull
566.Timing of Macro statements issue
567.Screen Flashing Problem
568.This Workbook contains links to one or more external sources [update] [don't update]
569.Windows(sWBK).Activate - Error 9 subscript error when New Window used and have :1 and :2 windows
570.Unwanted Dialogs popping up
571.IsError( ) - vba function
572.Application.DisplayAlerts = False ' get rid of unneeded dialog box
573.Application.EnableEvents = True/False
574.Error handling Logic - column doesn't exist
575.col variables - stores the number of a column which is retrieved via FindDataCol in case column moves (Hardcoding)
576.Error handling Logic - column doesn't exist
577.Err.Number
578.Processing Multiple Errors
579.LogAction - udf to write 3 fields to a sheet
580.Log2File udf
581.logit2(s) ' writes data to sequential file ' udf
582.Circular Reference
583.Compatibility Check
584.Hardcode then generalize on second occurance
585.Resume Next or Label - Error Handling
586.On Error Goto ErrHandler
587.=iferror
588." - "" - using quotes in a string
589.err <> 1004 and Error <> 40040
590.Err.Description
591.Hardcoding column number problem
592.lcol = Finddatacol("column header fields",1) ' avoid hardcoding by searching for a column
593.IsEmpty( ) vba function
594.err
595.Err.Clear
596.Err.Raise
597.mixing up row and column error
598.Final Else
599.Processing Multiple Errors
600.Error 2015
601.Error 2042 -
602.Error 5
603.Err <> 0
604.Error - Type Mismatch
605.Upper/Lower Case Common Error
606.Error File in use
607.Formula Error =C3-C2 and C2 is blank (if needed)
608.Copy formula c7 - B7 and get weird values and need to do =if(c7="","",C7-B7)🔝
609.Killing Excel
610.Timing of Macro statements issue
611.Application.EnableEvents = False
612.if a * b * c * d = 0 then msgbox "unexpected" (where a is a col lookup
613.When deleting work backwards: for x = sheets.count to 1 step -1 (loop)
614.WarnDev - udf - emails msg to default email loaded from a config "main_support_email" @ "company_domain"
615.Postitive "If" test vs. complex not

Chapter 32-Software Development Techniques
616.Table Driven Software
617.table driven software - change value is a table and get a new behavior (ex. list of items to process and add a new one)
618.Refactor - coding technique
619.Refactor code - that can be Table Driven
620.Hardcode then generalize on second occurance
621.Development, QA, UAT, Production Systems
622.Small to big - coding technique
623.Install Software in VBA
624.Backwards Compatibility
625.Design Patterns - MVC
626.Black Box Concept
627.JIRA
628.Interface Concept
629.Recursion
630.Redundancy Problem
631.4 Concepts of Object Oriented Programming - Interview Question

Chapter 33-Backup/Recovery
632.goto retry_it
633.Detect if user is running old version
634.CompatiblityCheck
635.GetLatestVersion(sfilename) - get the file with the latest version
636.GetNextVersion - udf - passed file and returns the next version of the file
637.GetSignature - gets a number based on the number of formulas + number of rows * cols
638.Archive

Chapter 34-Speed up/Optimize Excel
639.Timer
640.rt = udf to track Response Time (optimize)
641.rt - udf - Response Time
642.Application.CalculateFull ' recorded
643.LogWaitTime - track time my machines is unavailable due to PPT code running
644.Application.ErrorCheckingOptions.BackgroundChecking = False ' recorded optimize
645.Application.Calculate
646.Application.Calculation = xlCalculationManual ' recorded
647.Convert formulas to values
648.Mod - if iCnt Mod 10 = 0 then
649.Optimize - getlistcount(nnnnn) in a loop with 800 items
650.Application.ScreenUpdating = True/False ' used for optimization
651.Application.Calculation = xlCalculationAutomatic ' optimization
652.application.statusbar =

Chapter 35-Reporting
653.Large Spreasheets
654.Hide columns or rows on a report
655.Move columns on a report
656.FixTop25Other - a fix routine fixes something might have a small difference between version and standard
657.Display_the_columns - takes a spreadsheet and hides columns based on a range name - udf
658.addAITs
659.FinishDEReport - udf
660.Add Columns to a report
661.Add Columns to a Report Manually Maintained
662.Hide columns or rows on a report
663.Convert2link
664.Header Row
665.GetAllSlideNames
666.Different value issue a field in one system was KNA and another was ANA which meant the same so logic needed
667.RemoveCanceled - Removes rows with a status of canceled - udf
668.DQ Report
669.FormatMultipleSheets udf

Chapter 36-Interface with PowerPoint
670.diffs - udf - compares 2 ouputs
671.PPT Object Model
672.GetActivePPT - Function to return a reference to the Active powerpoint
673.Set PPT = CreateObject("Powerpoint.Application")
674.PPT.ActivePrenstation.Slides(2).select
675.CopyChartToPPT ... udf - very important
676.deleteSlide ' ppt routine
677.Generate_deck - udf - that runs commands in spreadsheet to generate ppt slides
678.PPT.ActivePresentation.Close
679.UpdateTOC - code to update ppt toc
680.Added after time separate process to add more slides with complex rules
681.For each shp in PPT.ActivePresentation.Slides(2)
682.AddRatingColumns
683.UpdateHyperLinkTOC - PPT
684.pptshp.HasTable
685.If pptshape.name like "xxxxx"
686.PPT.ActivePresntation.Slides.count - gets the last slide
687.pptshp.Table.Cell(i, 1).Shape.TextFrame.TextRange.Text - gets the text from a pptshp cell
688.pptshp.table.Columns.Count
689.PPT.Visible = True
690.pptshp.Table.Rows.count
691.deleteSlide ' ppt routine
692.Set ppttemplate = PPT.Presentations.Open(sFile, ReadOnly = msoTrue
693.Setting a Hyperlink on a cell in a ppt table
694.PPT.Presentations.Open sFile, ReadOnly:=msoTrue
695.PPT.ActiveWindow.Selection.SlideRange.SlideNumber - get the active slides #
696.PPT.ActivePresentation.SaveAs sFile
697.Set PPApp = GetObject(, "Powerpoint.Application")
698.PPT.ActivePresentation.Slides(n) - the nth slide oblect name to use in a with stmt
699.PPT test code to update embedded spreadsheet in ppt
700.Activewindow.Caption
701.Dim PPT As Powerpoint.Application

Chapter 37-Debugging
702.Stop
703.Using stop instead of error handling
704.Call Stack
705.End
706.i = i ' for debugging allows a place to set a break point while doing nothing
707.Debug.print variable - debugging - prints value to immediate window
708.Stop: resume next: Resume - Debugging and why?

Chapter 38-Email and Outlook
709.set iMsg = CreateObject("CDO.Message")
710.CheckScheduleSendMail - udf - email
711.GetUsersEmailAddress() - udf - internet
712.FixName - takes a passed name in Last, First and changes to First Last
713.EmailMsgBox
714.SaveAllAsPDFFile - Internet - https://howto-outlook.com/howto/saveaspdf.htm - Robert Sparnaaij
715.ColumnWidthToPixels - internet code (Data accuracy report)
716.SendChecklist_email() - udf - sends a formatted email as a checklist to proper person to dbl check report
717.MailReport
718.CreateHTMLTableForEmail (html, css,) - coverts spreadsheet visible data to HTML format
719.sendmail_cdo
720.sendmail_cdo udf
721.Attach a file to an email
722.GetUsersEmail
723.Selection.Characters.Text = "Add TOC" ' after selecting a button, you can change the text

Chapter 39-Testing 123
724.test_routine - code that tests other code
725.VerifyValue - udf - db - runs query based on Coper, field (ce,pe), date, returns value)
726.override date

Chapter 40-Pivot Tables
727.ActiveSheet.PivotTables("pt1").PivotFields("Acct_nbr").Autosort xldescending, sort ' recorded
728.ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ... ' recorded
729.ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFields 'recorded
730.ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = sValue
731.Hide PivotTable items
732.PivotFields("field_name").Subtotals = Array(False, False, ...)
733.ActiveSheet.PivotTables("pt1").PivotFields("coper_id").PivotFilters.Add Type:=xlTopCount, DataFiled:=... ' recorded
734.ActiveSheet.PivotTables("pt1").ColumnGrand = True ' recorded
735.PivotFields("field_name").Orientation = xlHidden
736.PivotFields("field_name").Orientation = xlRowField
737.ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh ' recorded
738.Activesheet.PivotTables("PivotTable11").PivotFields("CtryOfDom").PivotItems ' recorded
739.PivotFields("field_name").Position = 6

Chapter 41-VLookup
740.vlookup to external file - '[file name.xlsm]sheetname'!$C:$F
741.VLookup as part of a formula with """ multiple quotes issue
742.VLookup with RC[-44] from recorded code
743.=Ifna(vlookup(...
744.=ifna (xl func)
745.ActiveWorkbook.RefreshAll
746.Application.Evaluate("=vlookup(""" & ..... & """,'" & from ....

Chapter 42-Future Chapters
747.ThisWorkbook
748.Thisworkbook.path
749.Abstraction - Chess means what? or any term table, chair

Chapter 43-Input
750.Ask user via DispYN - (ex. run full version or "quick" one)
751.Application.Left
752.Application.WindowState = xlMaximized - recorded
753.Drop-down list in a Cell - Data Validation
754.inputbox

Chapter 44-Process - Run/Do Something
755.Click a button
756.Assign a value to a cell - Range("xxx") = "a value"
757.Application.Run - can be used to run a passed function on parameters - code in pptools - Callback_testing
758.Selection.OnAction = "TOC1.AddTocSheet" ' Select the shape first via ActiveSheet.Shapes.Range(Array("Button 1")).Select
759.A01 Dim WshShell As Object
760.ShellAndWait
761.Initiating Macros
762.Click an item in the Quick Access Toolbar
763.Run('c:\filename.xlsm'!Button2_click")

Chapter 45-Output
764.Msgbox - message box
765.Application.width
766.Appplication.Top
767.Application.Caption
768.Applicaton.WindowState = xlMaxmized ' recorded
769.Application.WindowState = xlNormal - recorded
770.Popup - udf (maybe put in batch section)
771.Popup - ui - udf - Display a msgbox that disappears
772.Click an Item on the Ribbon
773.RGB(x,y,z) - vba color func where x, y , z are # from 1 - 255
774.Color Dialog
775.Application.Height

Chapter 46-Subroutines and Functions
776.Sub - End Sub
777.Run a Macro with Parameters
778.functions vs. Sub
779.ByRef
780.ByVal
781.IsMissing(var)
782.subroutine parameters
783.Optional p2 ' declare a parameter as optional
784.exit sub ' used to prematurely exit a subroutine
785.Function
786.:= parameter
787.Calling a udf

Chapter 47-Charts
788.ActiveSheet.ChartObjects(sFromAddr).Activate
789.Highlight_PrevCurDiffs - udf
790.GetRegionalTopNames 6 parameters - udf - get a chart of top n based on CE or PE
791.Selection.Left or Top, Height, width, name after chartobject is activated
792.Returning a value(s) from a sub
793.Activesheet.ChartObjects(sToAddr).Delete
794.AdjustLabelPosition - udf - charts
795.True vs. False

Chapter 48-VBA Making Decisions with If and Conditionals
796.if then else end if
797.if - elseif - else - end if - and the final else
798.Option Compare Text
799.Or, And condition
800.Select Case
801.iif(num_rows = 20, 1, 0) - immediate if
802.> - greater than
803.Conditional - Or vs. And vs. Not , precedence, ( ) and nested
804.Conditional operators
805.Not
806.Applciatoin.UserName
807.if not x then

Chapter 49-Analyze Data
808.Monthly Processes (MoM files)
809.CheckListForMOM - Automates a checklist that person used to verify data
810.Nested If stmnt

Chapter 50-Range Names
811.Named Range
812.Range("range_name")
813.Activesheet.Names(sName).Delete ' deletes a named range
814.DeleteExternalNames - deletes named ranges starting with External_ ' recorded
815.Range("A1").Listnames ' puts named ranges in A1
816.ActiveWorkbook.Names("dataset").RefersToR1C1 = "=cpt_input_data!R1C1:R40C13"
817.fr = Range("first_row").Row

Chapter 51-Connecting to External Programs
818.Reference to Windows Script Host Object Model
819.Reference to Microsoft Forms 2.0 Object Library
820.As Word.Application
821.As Word.Document
822.Reference to Microsoft Data Objects 2.8 Library
823.Using vbe > tools > References
824.set wrdApp = CreateObject("Word.Application")
825.Microsoft Word (version) Object Library - Reference used
826.wrdApp,Quit
827.Tools > Reference
828.Set x = CreateObject("MSXML2.XMLHTTP")
829.Sendkeys "^A",True
830.as Outlook.Application
831.Reference Active DS Type Library
832.References - MSXML2.XLMHTTP60

Chapter 52-Documentation and Code Readability
833.Define Acronyms you use in comments
834.if .Cells(i,j) ' referencing the with - end with
835.commenting code
836.Excel Comments
837.Change Log

Chapter 53-Moving Around
838.ThisWorkbook.Activate
839.Goto Dialog - Ctrl+G
840.ActiveWindow.Activate

Chapter 54-Code that Edits Your Code
841.DeleteCodeModules -
842.DeleteServerSideCodeModules - udf report engine vbe
843.GetMacroCode

Chapter 55-Security
844.Hash - MD5 and SHA1
845.FileToMD5Hex - internet - security
846.FileToSHA1Hex - internet - security
847.LenB() - vba function
848.GetFileBytes - internet
849.stringtobytearray - internet
850.Password protecting your Macros
851.Obfuscate your code
852.CheckIfLocked - internet - code to check if the desktop is locked

Chapter 56-Dos commands
853.Dos - CD and cd ..

Chapter 57-Excel Options
854.R1C1 option

Chapter 58-Random
855.Get a Random number between 1 and a Million

Chapter 59-Math
856.Binary Numbers
857.Round - function
858.Positive (+) and Negative (-) Numbers
859./ - division
860.Int()
861.Abs(x) ' math absolute value
862.integers 1000 vs. 1000.0
863.Exponential Format
864.i = i + 1 ' increment a counter
865.Hexadecimal Numbers
866.Precedence

Chapter 60-External Applications
867.ComputerName - internet - Gets users computer name using ActiveDS.WinNTystemInfo
868.Install Software in VBA

Chapter 61-Sample UDFs
869.CountIfString(sCol,sValue) - counts the # of times the svalue appears in sCol

Chapter 62-Shortcuts
870.AR - sc for Activecell.row
871.Right1 - udf - move activecell 1 to the right
872.[=1+2] - runs an xl formula in vba
873.sCol = cl((c))
874.Ctrl+Shift+G - Google a Cell
875.Ctrl+Shift+Y - Yellow Highlight a Row
876.Ctrl+Shift+U/D- Move Cell Up or Down
877.Ctrl+Shift+R - Random Select
878.Ctrl+Shift+T - Tweet a cell
879.GotoXYZ - Shortcut key will go to the xyz system and substitute the #
880.Down1 - sub to move activecell down 1 row sc
881.GotoSheet - udf - for shortcut creation
882.Touch Typing
883.Width - End With
884.AHK to increase productivity (separate book)
885.AutoHotKeys
886.Pacing - Productivity Issue
887.cl - udf - converts a # to the AA format

Chapter 63-Wrapper Functions
888.AppLen - udf -wrapper - handles null value
889.Application.OnKey
890.AC - udf sc - Activecell
891.ShowSheet - udf - wraps the code to prevent errors
892.End_it - wrapper - call instead of End to trap where the end was called from and do something if End ocurs

Chapter 64-Sorting your Data
893.ActiveWorkbook.worksheets("sfile1").sort.sortfields.Add Key:=Range("N2"), SortOn:=xlSortOnValues, order:=xlAscending, DataOption=xlSortTextAsNumbers

Chapter 65-Forms
894.center in excel - application.width / 2
895.frm...show
896.frmLogger.show 0
897.Windows Controls - Combobox

Chapter 66-Shapes
898.Z-order
899.ActiveSheet.Buttons.Add(top,left,width,height).select 'not sure of the params
900.Selection Pane
901.Activesheet.Shapes.Range(Array("Button 1")).Select

Chapter 67-Bulletproof Your Spreadsheet
902.Protect/Unprotect a sheet
903.ActiveSheet.Unprotect
904.Protecting a Sheet

Chapter 68-Adjusting Data
905.Open two workbooks and apply adjustments from one workbook to the other
906.Data Adjustment Process at Monthend
907.Adjustment Logic

Chapter 69-Other Tools
908.Bloomberg
909.SharePoint
910.Hadoop
911.MicroStrategy
912.Tableau

Chapter 70-Formulas
913.=IfERROR(VLOOKUP(INDIRECT(ADDRESS(ROW(),VLOOKUP($BW2,'Field To Use'!$H:$O,6,0))),'Rating Lookup'!$A:$B,2,0),0)
914.Activecell.formulaR1C1 = "=If(RC[-32]=0,0,RC[-10]/RC[-32]
915.Debugging formula issues
916.Formula error =A1-B1/2
917.Evaluate formula tool

Chapter 71-Productivity
918.Pacing Tool to increase productivity

Chapter 72-Fill
919.Selection.Autofill destination:=Range("H2:I" & r)
920.Fill Down

Chapter 73-Appendix
921.Excel Special Characters
922.Range(...).value

Chapter 74-Other forms of VBA (vb, vb.net, vbscript,ASP)
923.ASP
924.VBScript

Chapter 75-Versioning
925.GettheVersion - opens workbook and finds the version and date by searching the sheet
926.Detect if user is running old version
927.LatestVersion - udf

Chapter 76-Areas
928.Selection.CurrentRegion.Select

Chapter 77-AI - Artificial Intelligence
929.AI BlackJack

Chapter 78-Useful Skills

Chapter 79-External Data Sources
930.Verify all data you get which helps when the format changes and it will new, changed, deleted
931.CSV file
932.After opening an external spreadsheet verify that it's okay - coding

Chapter 80-Logging
933.LogAction - udf to write 3 fields to a sheet

Chapter 81-Config/Settings/...
934.Get ini file - gsINIFile = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & ".ini"
935.Range("Settings!datasource_type") = "SEC"
936.Config

Chapter 82-Modules
937.SpreadSheetRoutines.bas
938.Import module file and there is a duplicate routine (I named the routine with a suffix "_dbs" for the module
939.copy modules in and finding all the syntax error

Chapter 83-Creating Games with Excel
940.Game - Board Game
941.AI - Checkers
942.Game - Card Game
943.Game - Concentration - Memory game
944.Game - Create a game

Chapter 84-Printing
945.PDFPrint
946.FormatFromTemplate - udf Report Engine
947.Self Learning
948.SetAutoFitMergedCell udf Report Engine
949.NLP - BS2-1 (desk)

   →Introduction | Table of Contents | Send us your feedback | Random | © 2021 All Rights Reserved | Edit