| 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) |