Data Access - VBA Techniques

00:00:00 Start
00:00:55 Programming - If, Loop, Assign ("ILA") or equivalent Conditional, Iterative, Assign ("CIA")
00:02:01 Ron Olsen - diagram showing basic breakdown of a program
00:03:23 Software Development Cycle - requirements, design, code, test, deploy, and maintain/support
00:03:55 Additional insights about the domain and programming environment are gained from each iteration
00:04:03 Code review - concrete examples of concepts
00:05:45 Procedural language vs Object Oriented Programming (OOP) styles
00:06:42 Recording Macros - good for automating steps
00:09:50 Functions and Subs - cell writing restriction, must initiate from a subroutine
00:12:52 Ways to call subroutines and functions
00:14:25 Passing parameters - ByRef (Excel VBA default), ByVal (Visual Basic VB.Net default)
00:19:37 Error when attempting to write to a cell from a function that is initiated from a function call in Excel
00:22:07 On Error GoTo ErrorHandler
00:23:07 ErrNumber, ErrDescription
00:24:05 OnError in a loop - must use Resume command
00:25:05 Worksheet Events - SelectionChange, Change, Calculate, Activate, DeActivate
00:28:17 USER change event
00:28:45 Cell changes in a range - FilterTest
00:32:32 Calculation control - QueryIt
00:33:47 Screen Update control - set to False before updating many cells, set to True after updating cells
00:34:01 Good practice to follow with DoEvents to ensure screen updating is complete
00:34:23 Wait for pending calculation to finish, CalculationState = xlDone
00:34:48 Moving data between Excel cells and VBA arrays
00:36:15 Demonstration code
00:37:21 Excel Transpose function - indexes array from 1 to n
00:38:16 Polaris TransposeArray function - only works on 2 dimensional arrays and uses the same indexing as the source array
00:43:55 Variant array indexed 1 to n, not 0 to n-1, when assigning an Excel cell range to a variant array
00:44:19 Performance differences between bulk and individual cell transfers
00:51:18 Moving array data between functions - Array, CSV, JSON all referenced as rows 1 to n and columns 1 to n
00:55:30 ReDim Preserve limits re-dimensioning to only last dimension of a populated array AND only allows changing upper bound, must establish lower bound before populating the array. Use ReDim to dynamically index an array as opposed to Dim which statically indexes an array. ReDim acts as a declarative statement if array not previously declared with Dim statement.
00:59:58 Global variables can be referenced from ANY module, but MUST be declared in a STANDARD module, NOT in a WORKSHEET object (Sheet1...)
01:01:47 Function Tracing - EchoBack function
01:06:22 Timing - GetTickCount (milliseconds), Update_Time (seconds), Spin, Wait_Loop, TimerLoop
01:09:36 Wrap-up

Видео Data Access - VBA Techniques канала Polaris Investing