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

Become an Σxpert at Σ


Standard VBA/Excel (1182)

1. a Order of Learning List
2. a Order of learning
3.FindDataCol - udf - 🔝
4. 19 Ways to Use Excel
5. 21 Important Excel Features to Know
6. Video Reminder (3)
7. Excel Features to Know to move from Beginner to Expert
8. 31 Common Excel Errors and How to Solve Them
9. 12 Errors People Make in Excel
10. a Excel/VBA for yourself vs. users of your sheets (18)
12. Versioning - Getting Excel to Save a new Version
13.Create Look - Idea - Dev Small - fix - works - New ideas/problems - Repeat
14.Compress Large Excel Workbooks
17.Dim sFile$, sZipfile, test as string
18.commenting code
20.Assignment stmt
22.Array -start with "a", array function
23. For loop - lbound ubound
24.reference value in an array aSuffixes(i)
25.On Error Resume next
26.concatenation - &
27. Replace
29. FileDateTime(sFileName)
30.nested functions replace(replace(sURL,
31.http, https.
32. err
33. <> - not equal, condition
34. if then else end if
35. Stop
36. Code indenting
37.Error 5
38.How to write a function to open a zip file when the file sometimes is zip and sometimes xlsb
39.File suffix - zip, xlsb, txt, xls, xlsx, xlsm, pdf, htm, html
40.exit for ' prematurely exit from a for loop
42. format
43. =
44. End
46.Now() - returns the date time
47.kill - delete a file
48. Variables
49.Msgbox - message box
50. DownLoadFileFromWeb(to,from) - udf
52. Creating a folder
53. UnzipAFile(file,directory)
54. ChDir - Files
55. CreateDirs
56.Date formatting yyyy-mmm-dd
57.File wildcard character * or ?
58.\ as a folder separator
59.Dir - files - process a directory or check if a file exists
60.Many ways to skin a cat
62.:= parameter
64.ActiveWorkbook after opening an excel file
65. VBA on 2 or more Excel files opened at the same time
66.OpenDQFile - udf to open the Data Quality file from web/sharepoint and unzip it
67. addAITs
68. FindDataCol("fieldname",row_nbr) udf - udf - converts a # to the AA format
70.(xxx) to convert to proper format - cok ??
71.Sub - End Sub
72.subroutine parameters
73.Insert a column - Columns(lCol).EntireColumn.Insert ' recorded
74. Concatenate a string to create a range
75.Assign a value to a cell - Range("xxx") = "a value"
76.Range vs. Cells
77. Select a cell - Range(...).select
78.Hardcoding column number problem
79.Using stop instead of error handling
80.Err <> 0
81.Filtering and visible rows
82. GetNextRow - filtering udf
84.Rows collection
85.Hidden property
87.Syntax errors
88. Small to big, one piece at a time
91. =iferror
93. " - "" - using quotes in a string
94.> - greater than
95.Conditional operators
97.Value vs. formula
98. instr( ) - find a string within a string and return the offset
101. Default property
102.if - elseif - else - end if - and the final else
103.Postitive "If" test vs. complex not
104.Add Columns to a report
105. Move columns on a report
106. Hide columns or rows on a report
107. Findlastrow - udf - finds the last row in a column
108. Misspelling error
109. mixing up row and column error
110.Selection. (formula, copy)
111.=ifna (xl func)
112.=vlookup (xl func)
113.vlookup to external file - '[file name.xlsm]sheetname'!$C:$F
114.Thisworkbook (xl reserved word)
115.range(cl(c+1) & fr & ":" & cl(c+1) & r).select
117. Or, And condition
118. Convert2link
119.nested for loops
120. Range("A1").select (dup)
121. Learn via Macro recorder
122.application.statusbar =
123.Select a column - Columns(i).select
124.Application.CutCopyMode = False - gets rid of crawling ants
125.Activecell.formulaR1C1 = .... - code from macro recorder
126. Cells(i,j).select
127.Selection.Copy - copies the active selection to the clipboard
128. Clipboard - basic
129. Cells
130.Rows - basic
131.Columns - basic
132. Terminology
133.Visible - basic
134. Hidden
135.Selection.Insert Shift:=xlToRight - recorded
137.Columns("A:A").ColumnWidth = 7.43 - recorded
138.FinishDEReport - udf
139. FindLastCol(1) - find the last column passed a row udf
140. AddBorders
141.ColumnWidthToPixels - internet code
142.Select Case - vba stmt
143.Sheets("name").select - selects a sheet
144. =match xl
145.application.evaluate(sLookup) - runs a vlookup or match or other excel statemnt
146.[=1+2] - runs an xl formula in vba
150. Paste
151.True vs. False
152. CountIfString(sCol,sValue) - counts the # of times the svalue appears in sCol
153.Range(selection, selection.End(xlDown)).Select - select area - recorded
154. CreateSheet "name" - adds a new sheet and deletes current one??? udf
155.Selection.autofilter = turns off filter
156.range(...).Autofilter Field:=1, criteria1:="=" & sValue, Operator:=xlAnd = recorded sets a filter
157. $A$1 vs. A$1 vs. $A1 vs. A1
158. =subtotal xl func
159.ColumnWidthToPixels - internet code (Data accuracy report)
160. Select Case
161. ByVal Single Integer
165.Function returning a value
166.Application.EnableEvents = False
170.<col width
171. <tr>
174. sendmail_cdo udf
175. range("range_name").copy
176. RunProgram "http.." (File Associations need to set up)
179. Delay 2 = udf
180.Rows("1:1").select 'a row
181.Selection.Delete Shift:=xlUp ' recorded
182. Workbooks("xxxx").Range("DER_cc") ' get the range name in another workbook
183.displaycolumns - udf
184. Maillto:
185.Open outlook email using runprogram and "mailto:"
186.Cells.autofilter field=lCol, Criteria1:="=*IBM*", Operate:=xlOr, Criteria2:="IBOR*"
187. RemoveCanceled - Removes rows with a status of canceled - udf
188. Add Columns to a Report Manually Maintained
189. AddLinks - convert Jira into links
190.Display_the_columns - takes a spreadsheet and hides columns based on a range name - udf
191. InArray(array,value) - determines if value is in the array - udf
192.Debug.print variable - debugging - prints value to immediate window
193.Ubound(array) - finds the upper bound of an array
194.LBound(array) - returns the lower bound of an array
195.ActiveWindow.FreezePanes = True/False
196.i = i + 1 ' increment a counter
197.Split(string,"value") - converts string into an array
198.Columns(1).select - selects a column
199.lcol = Finddatacol("column header fields",1) ' avoid hardcoding by searching for a column
200.Selection.cut - Clipboard
201. DownloadFileFromWeb(strURL, savetoPath) - udf using api
202. DQ Report
203.sCol = cl((c))
204.Rows(i).interior.color = vbYellow ' highlights row i
205.exit sub ' used to prematurely exit a subroutine
206.Selection.NumberFormat = "m/d/yyyy"
207.VLookup with RC[-44] from recorded code
208.With - End With
210. FixName - takes a passed name in Last, First and changes to First Last
211.Mid(string,start[,end]) - string function
212.Formatcols - Format the excel while recording
213.Borders - Record the code
214.GetHTML - code to get the source from a web page (doesn't work on pages that use ajax)
215.SaveStringtoFile string, "c:\xyz.txt" - saves a string to a file
216.References - MSXML2.XLMHTTP60
217.udf - Internet - GetHTML2 - good example of using DOM
218. Set IE = New InternetExplorer
219. Retrieving Data from a Webpage using Sendkeys String
221. IsMissing(var)
222.Optional p2 ' declare a parameter as optional
223.Sendkeys "^A",True
225.ThisWorkbook.Sheets(....).Range(....) ' use when a different sheet is active
226.Do While - Loop
229. ActiveWorkbook.Close False
230.GettheVersion - opens workbook and finds the version and date by searching the sheet
231.GetSignature - gets a number based on the number of formulas + number of rows * cols
232.FileLen(sFile) - gets the length of the file
233.GetVersionForECMTemplates - goes thru a directory and get the last mod and size of each file
234. Walk a file - get all files in a directory
235.GotoXYZ - Shortcut key will go to the XYZ system and substitute the #
236.GotoSheet - udf - for shortcut creation
237. GetLastModifiedData - gets the last modified data of a web page
239.As Object
240. On Error Goto ErrHandler
241.Set x = CreateObject("MSXML2.XMLHTTP")
242. Resume Next or Label - Error Handling
243.Selection.EntireColumn.Hidden = True ' recorded
244.Selection.PasteSpecial ... ' recorded
245.Selection.End(xlUp).select ' recorded
246.logit2(s) ' writes data to sequential file ' udf
247.bOpenSeqFile(sFile,"A") ' "O" output "I" input
248. Error(x) ' displays error message
249.Abs(x) ' math absolute value
250.txt files
251.Close #x ' Close a seq file
252. \\ ' network file prefix
253.Application.Calculation = xlCalculationManual ' recorded
254.Pastespecial pasteformats
255.Application.ScreenUpdating = True/False ' used for optimization
256.Doevents '
257.Application.Calculation = xlCalculationAutomatic ' optimization
258.Application.DisplayAlerts = False ' get rid of unneeded dialog box
259.ActiveWorkbook.SaveAs sFile
260. Date ' returns current date
261.Date = #1/4/2021#
262. Remove filtered data but keep filters
263.selection.PasteSpecial Paste:=xlPasteColumnWidths, ..' recorded
264.Rows("1:1").RowHeight = 52.5 ' recorded
265. vFileLen(sFile) ' udf to create an excel function to get the filelength
266.As Long
267.xl vs. VBA ranges
268.range names
269.ranges - "A1" "A1:C4" range_name
270.Mkdir - files - creates a new directory
271.Final Else
272.elseif err = 75 then ' directory already exists?
273.Goto Loop
274. Exiting loops
275.udf - CreateMultipleDirectorys from a list (files)
276. CreateHTMLTableForEmail (html, css,) - coverts spreadsheet visible data to HTML format
277. ActiveWorkbook.Worksheets("....").Autofilter.sort.sortfileds.clear ' clears a filters sorting ' recorded
278.functions vs. Sub
279.Applicaton.WindowState = xlMaxmized ' recorded
283.Appplication.Top in excel - application.width / 2
285./ - division
288.Calling a udf
289.errors - If it's broken you made a mistake (attitude)
290.VBA is a programming language, syntax
291.x = x + 1
292.Understanding someone else's vba code🔝
294. ActiveCell.Address
297. MergeCells one then do many
299.Small to big - coding technique
300. SaveAllAsPDFFile - Internet - - Robert Sparnaaij
301. Tools > Reference
302.Microsoft Word (version) Object Library - Reference used Outlook.Application
304.Bs Outlook.Selection
305.set objSelection = objOL.ActiveExplorer.Selection (not sure what this is)
306.set fso = CreateObject("scripting.filesystemobject")
307.As Word.Application
308.As Word.Document
309.set wrdApp = CreateObject("Word.Application")
310.A01 Dim WshShell As Object
311.Dim SpecialPath As String
312.A01 DocumentsPath = WshShell.SpecialFolders(16)
314.For each (loop) item in collection
315.Regular Expressions (see Save as PDF)
316. Trim - function string removed leading a trailing characters
317.ActiveSheet.Index - gets the sheets number (not sure why you'd use this
318.Global variables and variable scope
319. ShowSheet - udf - wraps the code to prevent errors
321. beforeSave
322.Public or Global variables
324. Adjustment Logic
325. End_it - wrapper - call instead of End to trap where the end was called from and do something if End ocurs
326. ThisWorkbook.Activate
327.After opening an external spreadsheet verify that it's okay - coding
328. SelectPartialSheetName - I wouldn't know the whole name of a sheet but knew part of it's name
329.if a * b * c * d = 0 then msgbox "unexpected" (where a is a col lookup
330.col variables - stores the number of a column which is retrieved via FindDataCol in case column moves (Hardcoding)
331.Different value issue a field in one system was KNA and another was ANA which meant the same so logic needed
332.End of Month issue - two system used different month-end dates problem
334. As Boolean ... True/False
335.i = i ' for debugging allows a place to set a break point while doing nothing
336.Round - function
337.Error - Type Mismatch
338.integers 1000 vs. 1000.0
341.Nested If stmnt
343. Load cell value to a vba variable
344. Refactor - coding technique
345.Right(string,n) - string function
347.FileCopy from, to
349. [cur_month_end] - access value in a range name
350.Range("A1:" & Cells(r,c).Address).Copy
351. Learn by using the Macro recorder
352.Open two workbooks and apply adjustments from one workbook to the other
353.FormatXyz - udf - to format data usually via recorded code,
354. Macro recorder hardcode removal
355.FixXYZFormat - the format of data needed to be fixed to a format that the code could process
360. GetUsersEmail
361. Application.IgnoreRemoteRequests
362.udf - LogAction
363.If Day(Now()) <= 19 then
364.Ask user via DispYN - (ex. run full version or "quick" one)
365.Conditional - Or vs. And vs. Not , precedence, ( ) and nested
366. Error 2042 -
367.Application.CalculateFull ' recorded
368.IsError( ) - vba function
370.Upper/Lower Case Common Error
371.Dim PPT As Powerpoint.Application
372.Set PPT = CreateObject("Powerpoint.Application")
373.Set PPApp = GetObject(, "Powerpoint.Application")
374.PPT.Visible = True
375.PPT.Presentations.Open sFile, ReadOnly:=msoTrue = Range("first_row").Row
378.rt = udf to track Response Time (optimize)
379. if .Cells(i,j) ' referencing the with stmt
380.GoTo next_for
381.deleteSlide ' ppt routine
382. Generate_deck - udf - that runs commands in spreadsheet to generate ppt slides
384.WorkBooks.Open sFileout, UpdateLinks:=False
385. ActiveSheet.ChartObjects(sFromAddr).Activate
386.Selection.Left or Top, Height, width, name after chartobject is activated
388.instr(lcase(..) )
389. Abstraction - Chess means what? or any term table, chair
390.Application.AskToUpdateLinks = false
391.CopyChartToPPT ... udf - very important
393. UpdateTOC - code to update ppt toc
394. SetAttr
395.PPT.ActivePresentation.SaveAs sFile
396. Get a Random number between 1 and a Million
397.Error File in use
398. PPT.ActivePresentation.Close
399.LogWaitTime - track time my machines is unavailable due to PPT code running
400.Stop: resume next: Resume - Debugging and why?
401.Delete a Sheet - turn off displayalerts before delete (sh.delete)
402. Error handling Logic - column doesn't exist
403.Create a Sheet with a number of columns to run queries and process Excel output
404. IsDataAvailable
405. RunQuery( ) - udf db - runs a query that returns 1 value and not a recordset
407.Application.Ontime Dateadd("n",30, Now(),"Sub_to_run_name" ' i
408. Setting Sheet
409.LogAction - udf to write 3 fields to a sheet
411.Application.Evaluate("=vlookup(""" & ..... & """,'" & from ....
412. err <> 1004 and Error <> 40040
413. LogSQLError -
414. Activecell.FormulatR1C1
416.Selection.Autofill destination:=Range("H2:I" & r)
417.Convert formulas to values
418.Selection.Pastespecial Paste:=xlPasteValues... 'recorded
419.Columns("H:I").NumberFormat = "#,##0"
420.Selection.Replace what:="#N/A", Replacement:="0", LookAt... 'Recorded
421. ColumnLetter - udf - converts a column # to a column letter
422.Errors from hardcoding
423. Sheets(2).visible = True
424.When deleting work backwards: for x = sheets.count to 1 step -1 (loop)
425. IsEmpty( ) vba function
426.InArray(array, the_value)
427.LoadArray 🔝
428. Redim Preserve a(r -2)
429. AddFinalSlash - udf - files
430.Application.EnableEvents = True/False
431.LBDOM - dates - udf - Last Business Date of the Month
432.A01 GTOD(e,offset, optional retDate) - udf Get Type of Date - c for Credit Studio, e for Everest
433.GTODList(type, string of offesets - ex. "0,1,2,3,6,9,12"
434.GetLatestVersion(sfilename) - get the file with the latest version
436.A01 CopyToDocumentumSweep - copies files to a network directory
437.GetFileName(fullpath) - returns the file name without directory
438. ActiveWorkbook.FullName
439.ActiveWorkbook.SaveCopyAs sFile
441.Change Log
442. Thisworkbook.path
443.Data_validation(sParms) - used for autorun receive request and run a macro
444.Return multiple values from a function by returning a comma separated sting and parsing the results
445.Attach a file to an email
446. GetFileCreator - internet - used shell to get the name
447. GetLastSlash(s) - File
448. GetUsersEmailAddress() - udf - internet
449.Len(string) - vba function
450.Print #x,string
451.Show directory via File Explorer - RunProgram "C:\directory_name"
452.Wait until a file appears
454. RunTheQuery - udf - db
455. SQL - Check return from query
456. SQL insert rows from data
457. CheckScheduleSendMail - udf - email Variant
459.DateAdd("s",Seconds, Now)
460.Do While (now < vStopTime) and gbStop = False
461.Sleep - API
462.iCnt = iCnt + 1
464.Mod - if iCnt Mod 10 = 0 then
466.Blink Excel Caption - batch
468.Colors in vb Editor - Green - comment - blue Excel statement ; red = syntax error
469.for i = Lbound(a) to UBound(a)
470. LoadArray(workbookname, Sheetname, header) 🔝
471. GenerateAllExcelAndDecks - batch - run multiple SQL's and Creates multiple PPT decks
472. Click a button
473.IsAutorun() - udf looks at the filename if has the name autorun then set flag to true
474.SECDataAvailable - udf - db - checks for a condition (cob date from db > month-end data) and send email, generates a file
475. test_routine - code that tests other code
476.Run "Thisworkbook.WorkBook_Open"
477.Return a value from a function
478. A01 IsRating - determines if a field is a rating range returns true/false
479.Trim( ) - vba function to remove leading and trailing spaces (dup)
480. Application.Run - can be used to run a passed function on parameters - code in pptools - Callback_testing
481. CheckListForMOM - Automates a checklist that person used to verify data
482.VerifyValue - udf - db - runs query based on Coper, field (ce,pe), date, returns value)
483.Embed a query in a function or sub
484. Clipboard routines
485.Declaring APIs
486. PPT test code to update embedded spreadsheet in ppt
487.ByteArrayToString - internet
488.As Byte
489.CheckIfLocked - internet - code to check if the desktop is locked
490.Protect/Unprotect a sheet
491. ActiveSheet.Unprotect
492.ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ' recorded
493. Protecting a Sheet
494.Collapse/Expand outline
495. ExcelInstances
496. FileToMD5Hex - internet - security
497.FileToSHA1Hex - internet - security
498.GetFileBytes - internet
499. Freefile
500.LenB() - vba function
501.Open .. for binary access
503.Erase ... vba erase (g)
504.stringtobytearray - internet
505. Hash - MD5 and SHA1
506.=IfERROR(VLOOKUP(INDIRECT(ADDRESS(ROW(),VLOOKUP($BW2,'Field To Use'!$H:$O,6,0))),'Rating Lookup'!$A:$B,2,0),0)
507. Added after time separate process to add more slides with complex rules
508. AddRatingColumns
509.GetActivePPT - get a reference to active ppt
510.Cells.Clear - Clear all cells
511.Workbooks(sWB).Close False
513.ProcessSQL - udf - very important 🔝
514. GoTo
515.Activesheet.PivotTables("PivotTable11").PivotFields("CtryOfDom").PivotItems ' recorded
516. Hide PivotTable items
517.Refactor code - that can be Table Driven
519.Use file name to denote something like "-quick" isn't the full result
520.Set ppttemplate = PPT.Presentations.Open(sFile, ReadOnly = msoTrue
521. ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ... ' recorded
522.ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh ' recorded
523. Naming convention (26)
524. ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFields 'recorded
525.ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = sValue
526. Rows("9:34").EntireRow.AutoFit
528. SaveStringToFile - udf 🔝
529. RunSQLFromFile - udf - db 🔝
530.Run SQL from a file
531.Range("Settings!datasource_type") = "SEC"
532.Run SQL and save output to a sequential file
533.GetNextVersion - udf - passed file and returns the next version of the file
534. GenerateExcelFiles from a db - udf
535.Selection.WrapText = False ' recorded
536.ActiveSheet.Copy After:=Workbooks(sMainWBK).Sheets(Sheets.count)
537. _ - continuation character
538.ActiveWorkbook.Names("dataset").RefersToR1C1 = "=cpt_input_data!R1C1:R40C13"
539.ActiveWindow.SelectedSheets.Delete - recorded to delete multiple sheets (displayalerts=false) to turn off warning
542.Selection.NumberFormat = (dup)
543. Selection.NumberFormat = "mmm-yy"
544.Selection.NumberFormat = "mmm-yy" (dup)
545.Selection.Numberformat = "#,##0.0,,,"
546.AdjustLabelPosition - udf - charts
547. Hardcode then generalize on second occurrence
548.GetRegionalTopNames 6 parameters - udf - get a chart of top n based on CE or PE
549.Returning a value(s) from a sub
550.FixTop25Other - a fix routine fixes something might have a small difference between version and standard
551.Format dates
552.for k = 1 to 12 step 3 ' loop
553.RGB(x,y,z) - vba color func where x, y , z are # from 1 - 255
554.Selection.Font.Underline = xlUnderlinestyleSingle
555.a filename can't use certain characters line "/" so change it if creating it from data
556.Sheet names have size and character limitations
557.Err = 53 ' file not found
558.sheets vs. worksheets collection?
559.SetAttr sFilename, vbReadOnly
560. FindDataRow("string",col_nbr)
561.Do - Loop ' no while needs Exit Do
562. table driven software - change value is a table and get a new behavior (ex. list of items to process and add a new one)
563. ActiveSheet.PivotTables("pt1").PivotFields("Acct_nbr").Autosort xldescending, sort ' recorded
564.ActiveSheet.PivotTables("pt1").PivotFields("coper_id").PivotFilters.Add Type:=xlTopCount, DataFiled:=... ' recorded
565.ActiveSheet.PivotTables("pt1").ColumnGrand = True ' recorded
569.iif(num_rows = 20, 1, 0) - immediate if
570.Selection.Address - vb property - returns the $A$1 formatted address of the selected cell(s)
571. Dim FileSystemObject As New FileSystemObject
572.FilesSystemObject.CopyFile sfrom, sTo
573.dir(filename) = ""
574.PivotFields("field_name").Orientation = xlRowField
575.PivotFields("field_name").Position = 6
576.PivotFields("field_name").Subtotals = Array(False, False, ...)
577.PivotFields("field_name").Orientation = xlHidden
580.Verify all data you get which helps when the format changes and it will new, changed, deleted
581. Define Acronyms you use in comments
583.AC - udf sc - Activecell
584. AddFieldToDB (sfield, sTable, stype)
585.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
586.AppLen - udf -wrapper - handles null value
587. AR - sc for Activecell.row
588. auto_open - udf
589.Application.OnKey 0
591.Set gADO = GetConnection(GetDatasource())
592.Popup - ui - udf - Display a msgbox that disappears
593.Application.ErrorCheckingOptions.BackgroundChecking = False ' recorded optimize
594. Application.Autocorrect ' record
595. check_include - udf db implements sql include logic
596. CheckDatabase - udf
597. CheckForUnadjustedSQL(sSQL, sNote) - udf db looked at SQL and searched for a table that shouldn't be used
598.ComputerName - internet - Gets users computer name using ActiveDS.WinNTystemInfo
599. CreateQueryTable - db -
600. DeleteExternalNames - deletes named ranges starting with External_ ' recorded
601.Range("A1").Listnames ' puts named ranges in A1
602. Activesheet.Names(sName).Delete ' deletes a named range
603.Import module file and there is a duplicate routine (I named the routine with a suffix "_dbs" for the module
604.Down1 - sub to move activecell down 1 row sc
605. DropFieldFromdb - udf
606.Email ' udf internet
607.set wb = ActiveWorkbook
608. set iMsg = CreateObject("CDO.Message")
609. EmailMsgBox
610. GetConnection - udf - db
611. GetDataSource - udf - db
612. GetRecodset (sSQL, Optional maxRecords) as ADODB.Recordset '
613. GetSystemVar - udf - db - Retrieves a System Variable from a table
614.Insert_to_dataissues_click - Loop thru data on sheet and insert it to a table
615. QF - db -udf - Query Fix to replace an odd # of quotes with even number to stop sql injection attack (security)
616. LoadData(sFile) - udf - loads a file's data to a string
617.Do While Not EOF(x)
618.Line Input #x, sData
619. LoadListBox(c as object, sSQL as String) - db - udf -
620. LoadQueryToString - udf - db
621. LoadSQLtoSheet
622. Popup - udf
623. ProcessSQL(sSQL, sSheet, optional sProcess
624.LatestVersion - udf
625. CompatiblityCheck
626.Detect if user is running old version
627. Type fiilter_Fields row as Long column as Long End Type
628.Public gsFilters() As Filter_fields 'Filter fields is a udt
629.Holding the shift key when excel starts stops macros from running
631.rt - udf - Response Time
632.As Boolean (duplicate)
634.Logautorun - udf
635.Dim lStart as Long, lGo as Long, ..
636.for z = len(sSQL) to 1 step -1
637.Asc( ) - VBA string func converts s to a number
638. Exit Do
640.A01 vbLF
642. SQL Create
643.SQL insert
644.SQL truncate
645.SQL delete
646.SQL update
647.SQL drop
648. Log2File udf
649.goto retry_it
650. RealGo - udf db - detmine if a go in sql is the real one
651. RemoveLastComma
652. RemoveSQLComments - udf
653.Right1 - udf - move activecell 1 to the right
654.Dim rs as New ADODB.Recordset
655.If gADO is Nothing then
656.Set gADO = GetConnection(GetDatasource())
657.If cn.State = adStateOpen then ' db
658.check_include(sSQL,"") - udf db
659.if rs.fields.count = 1 then
660. If rs.EOF then
661.vntResult = rs(0)
662.for i = 0 to rs.Fields.Count - 1
666.Set rs = nothing
667. runquery2 - udf - runs a query that returns 2 values
668. As Worksheet
669.As Excel.Range
670.Dim a() as string
673. GetCommandLine() - udf
674.WorksheetFunction.Min( ) I use application.evaluate
675. Using vbe > tools > References
676.Reference to Microsoft Data Objects 2.8 Library
677.Reference to Microsoft Forms 2.0 Object Library
678. Reference to Windows Script Host Object Model
679.Reference Active DS Type Library
680. Constants - Const SW_SHOW = 1
681.API - Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA(ByVal hWnd As Long, ...
682. public gADO As ADODB.Connection
683.API - Public Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
684.Option Explicit
686. Error 2015
687.LTrim - VBA func
688.RTrim - VBA func
689.Is Nothing - Objects?
690.if not x then
691. SendChecklist_email() - udf - sends a formatted email as a checklist to proper person to dbl check report
693. LoadFileToString(sFile) - udf
694.UseProductionDB vs. UseTestDB - udf - that set a global
695.WarnDev - udf - emails msg to default email loaded from a config "main_support_email" @ "company_domain"
696. WebQuery - internet - udf - run a url and loads the output to a worksheet - recorded
697.WhatDB() - udf - queries the gADO.Properties(9) and if an error occurs returns "Not Connected"
698. WildCardSelect(...) - db - udf - adds fields to select query based on wildcards - cool
699.Option Base 1
700. Option Compare Text
701.Seletction.Style = "Percent" ' recorded
702. Activecell.formulaR1C1 =
703.Activecell.formulaR1C1 = "=If(RC[-32]=0,0,RC[-10]/RC[-32]
704.s,Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ::-""??_);_(@_)" 'recorded (g)
705. align_ok_key(lKeyCol1 as Long, range1, lkeyCol2 as long, range2) - udf - 🔝 Aligns data from 2 sources
706. CompareTwoFiles() - udf
707. diffs - udf - compares 2 ouputs
708. ActiveWorkbook.worksheets("sfile1").sort.sortfields.Add Key:=Range("N2"), SortOn:=xlSortOnValues, order:=xlAscending, DataOption=xlSortTextAsNumbers
709.formatting routines - almost all are recorded and modified
710. GetStats - udf
711. Highlight_PrevCurDiffs - udf
712. Year(vDate)
714. A01 doc "text" ' udf documents the code
715.Weekday - dates
718.override date
719.DateAdd("m", -1, vDate))
720.GTOD - GetTypeOfDate - e e-system C c-system
721. GTODList(sStype, aOffsets) - date Get Type of Date list format "3,6,12,15" a list of prev offsets
722. IsBizDay(vDate)
724.fdom - first day of month -date udf
727.Do Until n = 0
728.Learning by stepping through code🔝
729.Application.WindowState = xlMaximized - recorded
730.Application.WindowState = xlNormal - recorded
732. Debugging with Msgbox's
733.Debugging with Debug.Print
734.VLookup as part of a formula with """ multiple quotes issue
735. Call Stack
736. Enable/Disable Macros
737.Workbook_open - event
738.Workbook_close - event
739.Get ini file - gsINIFile = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & ".ini"
740.Open iniFile For Input As 1
741.While Not EOF(1) ... Wend - loop
742.Gosub routine_name
743.InitDB - udf to initialize a database
744.A01 CreateWorkspace - vba
745. Workspace.OpenConnection
748.Connection Sting
750. Call routine_name ' call is optional but clearer to new users
751.Ctrl Shift F2 - Go back to previous routine in VB editor
752.A01 Dim rsCOB As DAO.Recordset
753. Run a query sample
754. GetParameters - udf - Report engine
755. OpenRecordset
756. B01 xmlLoadData - udf
757.Private scope
758.Scope of variables
759.As DAO.Recordset
761. ShellAndWait API
764.A01 FormatFromTemplate - udf - Report engine complex Range
767. ClearClipboard - udf - Report Engine
768. Modules
769.Module variable scope
770. DeleteCodeModules -
771. DeleteServerSideCodeModules - udf report engine vbe
772. Footer
773. A01 FormatforPrinting - udf - report engine
774. FormatFromTemplate - udf Report Engine
775. FormatMultipleSheets udf
776. GetMacroCode
777. IsArrayEmpty - udf
778. SetAutoFitMergedCell udf Report Engine
779. Activecell.mergecells
780. activecell.MergeArea
782. MailReport
784.ActiveSheet.Buttons.Add(top,left,width,height).select 'not sure of the params
785.Activesheet.Shapes.Range(Array("Button 1")).Select
786.Selection.OnAction = "TOC1.AddTocSheet" ' Select the shape first via ActiveSheet.Shapes.Range(Array("Button 1")).Select
787.Selection.Characters.Text = "Add TOC" ' after selecting a button, you can change the text
788.#If Win64 Then Declare PtrSafe .(old 32 bit declare) #else Declare ...(old 32 bit declare) #end if
789.32 bit vs. 64 bit
791. Making Spreadsheets more Professional Looking
792. byRef argument type mismatch - syntax error
793. GetAnchorFromCell - udf - web
794. Run a Macro with Parameters delimited file
796.CSV file
801.Workbooks Collection
802. ThisWorkbook
803.Sheets Collection
804.Named Range
805.Using Multiple Workbooks
806.Sheets aka WorkSheets
807.Rows, Columns and Cells
808. Initiating Macros
809.#NAME error
810.#REF error
813.Hexadecimal Numbers
814. Binary Numbers
815. Monthly Processes (MoM files)
816.Data Adjustment Process at Monthend
817.Using Database Views
818.Header Row
819. XML File format
821. You Use To Learn on Your Own
822. Bloomberg
824. R1C1 option
825.Windows Job Scheduler
826. RetrieveDataToSheet - udf - db
827. SQLStuff.bas
829. ErrHandling.bas
830. Quit Excel- Application.Quit
831.Circular Reference
833.Windows Controls - Combobox
834. Dos - CD and cd ..
835.Password protecting your Macros
836. JIRA (g)
837.Drop-down list in a Cell - Data Validation
838.Outline/Group/Ungroup data
839.Subtotal function - excel
841.Exponential Format
842.Static variable
843.if Error = "[Microsoft] [ODBC Driver Manager] Data source..." Then after set gADO and ask to install driver
844.ODBC Dialog
845. Install Software in VBA
846. Processing Multiple Errors
848.PPT.ActiveWindow.Selection.SlideRange.SlideNumber - get the active slides #
849.PPT.ActivePresentation.Slides(n) - the nth slide oblect name to use in a with stmt
850.For each shp in PPT.ActivePresentation.Slides(2)
851.If like "xxxxx"
855.PPT.ActivePresntation.Slides.count - gets the last slide
856.pptshp.Table.Cell(i, 1).Shape.TextFrame.TextRange.Text - gets the text from a pptshp cell
857. Setting a Hyperlink on a cell in a ppt table
858.UpdateHyperLinkTOC - PPT
859.Learn via Immediate Window
860.Learn via sample code
861.Learn by going from small to big
862.View a directory ' runprogram on the Sharepoint directory name
864.Other forms of VBA (vb,, vbscript)
865.Killing Excel
866.Copy formula c7 - B7 and get weird values and need to do =if(c7="","",C7-B7)🔝
867.Fill Down
868.Development, QA, UAT, Production Systems
870.Excel Comments
871.Pacing Tool to increase productivity
872.AHK to increase productivity (separate book)
873.Formula error =A1-B1/2
874.Formula Error =C3-C2 and C2 is blank (if needed)
875.Filter Problems copying moving...
876. #VALUE!
877.######## - doesn't fit or negative date
878.Error 2015
880.Evaluate formula tool
881.Debugging formula issues
882.Create syntax error to save a location you want to go back to
885.Touch Typing
886.Optimize - getlistcount(nnnnn) in a loop with 800 items
888.Download website code
891.Web Editor
893.Create HTML files
894.Download Web pages
895.Large Spreasheets
897.Copy Transpose
898.Goto Dialog - Ctrl+G
899.#3/22/2021# = Date Constant
900.Game - Concentration - Memory game
901.AI - Checkers
902. AI BlackJack
903.This Workbook contains links to one or more external sources [update] [don't update]
904.Array(string, string, ....) - Converts a list of strings to an array
905.Subscript out of range
906.List of terms to know
907.Parts of a URL
908.Obfuscate your code
909. You Don't Have to Understand Everything
910.Color Dialog
911.Click an Item on the Ribbon
912.Click an item in the Quick Access Toolbar
913. Pacing - Productivity Issue
915.Learn to Juggle
916. Backwards Compatibility
917.copy modules in and finding all the syntax error
918.Game - Card Game
919.Game - Checkers
920.Game - Board Game
921.Game - Create a game
922. Create a Drop-down web menu using Excel
923. Timing of Macro statements issue
924.Redundancy Problem
926.Windows(sWBK).Activate - Error 9 subscript error when New Window used and have :1 and :2 windows
927.Selection Pane
928.Finding the Macro being Recorded
929.Screen Flashing Problem
930.Black Box Concept
931.Interface Concept
932.Normalized Forms - db
933. 4 Concepts of Object Oriented Programming - Interview Question
934.Large Arrays - Interview Question
935.Design Patterns - MVC
936.The 4 Main VBE Windows and the Rest
937.What you can record and what you can't (loops, decisions, flexibility)
938.Recording a Macro and Generalizing it
939.PPT Object Model
943. Basic Excel Functions to Know
944.Arrays vs. Collections
945.Project: SQL UI
946.SSRS - SQL Server Reporting Services
947.Versions of Excel
948.xla files
949.xlt Files
950.Learn Excel and Extract data from the Web
951.Learn Excel and Turn Ideas to Money
952.Job Scheduler
953.Extract Links
954.Button press doesn't work - You're still debugging the macro
956.Project Explorer
957.Hierarchy Control to Show [+] or Hide [-]
959.Go to First or Last Sheet
961.Clear Method - Home > Editing (5 options)
962.Determine Macro Associated with a button
965.Large Nested-If with missing end if or Else statement
966.Stopping Running Code - Ctrl Break
968.Calculation Manual
970.Date as a Number - 44520
971.Manual Calculation Problem
972.Calculation Option
973.Hidden Shape Problem
974.For Loop with Birthday Problem
976.Compatibility Mode
977.Power Pivot
981.Forecast Sheet
982.3D Map
983.Inserting Objects
985.Game - 20 Questions
986.Birthday Problem
989. Confusing in Excel
990. Viewport concept of Excel
991.CDbl - Convert to Double
993.Out of Memory
994.^ - Exponentiation
996.Immediate Window
997.Virus aka Macro Virus
998.Level of Understanding
1001.Division - /
1002.Cusip Problem
1003.Leading 0 problem and Excel hides it
1004.Special Characters
1005.Table in Excel
1006.Hidden Workbook
1007.Hide/Show Shapes - Ctrl+6
1008. ActiveWindow.DisplayWorkbookTabs = True
1009.Power BI
1010.Macro to Turn on Automatic Calculations
1011.Freeze Frame Problem
1012.Use Freeze Panes to lock info like buttons or totals
1013.Hide columns that confuse user (hide or grouping)
1014.Drag and Drop
1015.List of Windows Controls or Objects to be Familiar With
1017.Excel Versions
1019.Show Formula Mode - Ctrl `
1020.List of Options to Be Familiar With
1023.Stack - Used in Call Stack
1025.Crawling Ants
1026.Formula vs. Value vs. Format
1027. Algebra vs Programming Assignment Statement
1028.Positive (+) and Negative (-) Numbers
1029.Formula Bar
1030.Learning new things is stressful
1031.ALED - A Little Each Day
1032.Shapes, Smartart, Screenshots
1035.Manage Data Model
1036. Work and Learn
1037.New Phone Experience
1038.Check Input Data
1039. Get Address of a Range name
1040.Indirect Function
1041.Custom Lists
1043.Searching for Excel/VBA Answers
1044.What's the most common statements I use in macros in top 10 - 1 order
1045.Analogy between programming language and real language
1046.Summary Statistics
1048.Automate Manual Tasks
1049.Companies Have Unique Language - first day on the job story
1051. Pause Running Macro - Esc or Ctrl Break
1052.What VBA commands aren't supported in ASP?
1053. Create sheets from a list
1054.Tweet in Excel
1056.Learn via Google
1057. Cancel or Pause a Batch
1058.Pain Point
1059.Esc key
1061.Macro Options
1062.Definition of Done
1063.Cells, Rows, Columns and Ranges
1064.Add Hyperlinks to a Report
1065. Testing you Spreadsheet or Macro
1066.Running a batch and opening a new spreadsheet Issue - IgnoreRemotedde
1067.Release Notes
1069.Hidden Rows in Excel
1070.Company Language Issues and Excel
1071. Using fx to understand a formula
1073. version compatibility issue
1074. Student Knowledge
1075. Website problem
1076.Excel -x
1077.Debugger most used to least used list
1078.Restore Point
1079. PasteValues
1080.32 bit vs. 64 bit
1081.Syntax Checker
1083.Delete a Control you need to be in Design Mode
1084. Understanding Bitcoin Using Excel/VBA
1085.Don't hardcode
1086.Range("C4") = Range("C4")
1087.sheet names
1088.values in strings
1089.if x = "scott" then
1090.Lower case/upper case bug
1091.Column references
1092.If input file format changes
1093.Missing Error Handling problem
1094. Standard error handling
1095.Date is a string problem
1096.Date filtering problem due to 12/1/2021 vs. 12/01/2021 vs. 7/2/21 vs. 07/02/2021 vs. 7/02/2021
1097.Missing error handling and premature exits
1098.Input file format is changed
1099.Hardcoding problem but physics w/o friction issue to simplify
1100.Early Warning Report
1101.Tic-Tac-Toe program that learns
1102.Health spreadsheet
1103.Networking spreadsheet
1104.Goal spreadsheet
1105.Learning language at the new job
1106.Status while running a process so user doesn't think it hung up🔝
1107.Covert Text date to date - cells(x,y).formula = cells(x,y).value (for all rows)
1108.We can't update some of the links in your workbook right now ... [continue] [Edit Links...] dialog
1109.Microsoft 365 Training
1110.Date issues show up over course of year, new year logic, 9 to 10 month or day
1111.Plan to get book/training done
1112.Excel communicates with web - Gets ReleasePro data
1113. 3 Excel Tricks
1114. 16 Most Important Excel Functions to Know
1116.Labels for Goto
1117. FindWorkbook(sWB) udf
1118. GetDirName(sFile)
1119. SendMailto( ) udf
1120. FixEmail - Take an email address with < > and remove this
1121. DeleteSheet
1122. DeleteSheets
1123. FindDataRowPartial(sSearch, c) as Long
1124. GetFirstnameFromEmail(s)
1125. Logit
1126. PreviewEmail
1127. RemoveRangeNames - Removes ones with errors
1128. SendEmailFromList - Goes thru a spreadsheet and Sends emails
1129. SheetExists - udf
1130. WrapEntireRowDelete
1131. Problems
1132.Pause processing and verify data
1133.Find Missing Data
1134.Check a result and take an action
1135.Ask user a question and proceed from their answer
1136.Preview emails before sending
1137.Use a macro before writing one
1138.Initiate a task via a button press
1139.Remove people from email who are no longer at your company
1140.Logger - user defined shortcuts
1141.AreTheyTheSame - internet function udf to check if 2 files are the same
1142. CountFiles(sDir, sFilter)
1143. CreateDirectories
1144. OpenExcelWorkbook(sFile)
1145. OpenFile() udf
1146. ExtractData function
1147. GetDataFromLink(sLink, sEpic, sDeliv)
1148. GetDeliverables
1149. GetJira
1150. RenameFiles
1151. GetRester
1152. InsertStoryOrBug
1153. InsertSubtaskOrSubbug
1154. progressWorkflowAction
1155. UpdateJIRA
1156. Coding Standards
1157. CountUnique
1158. Flashing while VBA is running
1159.11/12/14 msgfromDoug.xls - used with Outlook to pop up when boss contacted me
1160.11/12/14 Load Zinc data to server
1161.11/12/14 Plan.xlsx
1162.11/12/14 Personal.xlsb
1163.11/12/14 GetCode
1164.11/12/14 Text Marked Post
1165.11/12/14 Time Create
1166.11/12/14 Determine Size X Diff
1167.11/12/14 Compare Sheets
1168.11/12/14 Micar.xlsm xl, db access
1169.11/12/14 Random XL, vlookup
1170.11/12/14 Addins
1171.11/12/14 Shortcuts xl, web queries
1172.11/12/14 Warning External Refs
1173.11/12/14 Server Debugging
1174.11/12/14 Logging
1175.11/12/14 Delete Spam
1176.11/12/14 Infografic
1177.11/12/14 Excel Diet
1178.11/12/14 Logger in XL
1179.11/12/14 Reports
1180.11/12/14 Filter
1181.11/12/14 Pivots
1182.11/12/14 Charts