What Can Excel Do (WCXD)?Highly Used routines/functions- FindLastRow()
- Delay n - delay for n seconds to wait or correct timing issue
- RunProgram file - Opens website, file, etc.
- DispYN( text)
- PasteValues
- Convert a column number to a letter - ColumnLetter( )
- FindDataCol("data",lRow)
- FindLastCol(row)
- cl(n) - columnLetter shorter version
- GetUniqueValueCount(col#,header Row)
- diffs(key1Col,sCols,key2Col,sCols2,startRow)
- Align_on_key(key1Col,range1,key2Col,range2,
- IsLatestFile()
- GetFileName()
- GetFileSuffix()
- OpenFileAndLoadToWB()
- If dispyn() then
- FindDataCol(sLookup,lRow)
- DeleteSheet "name"
- Stop_it
- End_it
- FindDups
- GetColLetter(sField,lHeaderRow ) - returns the Letter where sField is in the lHeaderRow
- GetUniqueValueCount(col,lHeaderRow)
- CreatePivot
- GetLastCellAddress() - selection.specialcells(xlCelTypeLastCell).Address
- GetUsedCells - get last cell get last row, last col and return row * col
- ColumLetter( )
- CreateSheet "name"
- CountValue(sField,sValue,lHeaderRow) ' counts the # of times sValue in the sField column
- More
VBA Shortcuts- Toggle between Excel and VBA - Alt+F11
- Go To Immediate Window - Ctrl+G
- See contents of variable - Ctrl+G > ? var
- Project Explorer - Ctrl+R
- Properties - Ctrl+F4
- Run/Continue - F5
- Step Over (don't show statements in a routine) - Shift+F8
- Object Browser - F2
- Step out of routine - Ctrl+Shift+F8
- Call Stack - Ctrl+L
- Set Next - Ctrl+F9
- Last Position - Ctrl+Shift+F2
- Application.OnKey "^s","Routine_to_run"
- Assign a Macro to a shortcut key
- Logger type shortcuts
- Stops executing VBA - Ctrl+Break - (Esc) also works
- Google activecell ` Ctrl+G
- Autocorrect based on sheet and column
Sheets- CreateSheet "sheet name"
- Max of 30 characters
- Some characters are not allowed
- SheetExists("sheet name") - Returns True if sheet exists else False
- ShowSheet "sheet name" - Shows sheet and makes visible if hidden
- Very hidden sheet
- SheetExists()
- DeleteSheet()
Conditional Logic- j = 6
- j > 6
- j <> 6 ' value of j is not equal to 6 the true (1-5, 7 - n) otherwise false
- And - j>6 and j < 10 ' true if j is between 6 and 10 but not 6 or 10 Both must be true otherwise false
- Or
- Parenthesis ( ) and complex (a = b) and ((c = 7) and d = 5)
- =
- <>
- >
- <
- <=
- >=
- i Mod 100 = 0
- And
- Or
- Expressions (a+b)/2 * 3
- Like
- Wildcards
- True/False
- 0 or non-zero
- iif(cond,true statemnt, false statement)
Sorting- Use Macro recorder
- Sort via dialog
- Sort via filters
- Sort via VBA code
- Sort via SQL
- Sort by user defined values
| Programming/Coding- What is programming? (g)
- Recipes
- Flowcharts
- Code-Test Loop
- 5 basics - sequence, if, loops, subroutines, functions
- Algorithms
- Programming Concepts
- Flags
- Toggle code - x = not x
- Increment code - x = x + 1
- concat loop code - sString = sString & sNewData
- Subroutines vs. functions
- Test_routine
- Aligning code
- Refactoring
- Fix Save Move Test (FSMT)
Forms- VBE (Alt+F11) > Project Window (Ctrl+R) > Right Click > Insert > UserForm
- Add Controls via View > Toolbox
- Double-click buttons to add code
- frm.Show
- Unload frm
- Form Events
- TextBox
- Creating a VBA Form
- Adding controls to a form
- Show 0
- Modeless vs. Modal
- frm.top
- frm.left
- frm.Repaint
- Labels
- TextBox
- Combobox
- ListBox
File Processing- Mkdir - to create a directory
- bOpenSeqFile(file,typ) I - input, 9
- Print #x,sVar - writes to open #x file
- Line Input #x, sVar
- Close #x
- Do while not eof(x) .... Loop
- Dir( ) - does file exist used in loop to get next
- FileCopy f1,f2
- kill sFile - Delete a file sFile
- Name sfile as new_file - to rename a file
- Get Files in a Directory
- LoadFileToString and SaveStringToFile
- rmDir "directory" ' have to remove files first
- SetAttr path, attributes
- AddFinalSlash(sFile)
- Get Directory from user or auto determine
- Create a set of 10 directories each month
- Process all files in a directory
- Filelen(path) - returns file size
- Create a file
- Line Input #x,svar
- GetDirName(sFile)
- GetFileName( )
Common VBA Problems/Errors- Spelled something wrong (may be most common error)
- Syntax Error
- Space convert number or date to string
- Timing Issues
- Slow code
- Hardcoding
- Works on your machine but not on users
- Can't stop code - add DoEvents to loops
- Dialog box showing - application.displayalerts = false
- Links to one or more external sources
- Hard to solve Syntax errors
- Compile error ByRef argument Type Mismatch when calling subroutine or function put ( ) around calling value
- Quote within a Quote use two quotes ""
- Missing Error Handling
- Upper/Lower Case Issues
- Global variable changed by called routine
- Status bar not updating - add DoEvents
- Workbook too large
- Using a reserved word in code
- Event executes and you don't want it to - application.enableEvents = false
- Stop Spreadsheet from flashing - Application.screenupdating = False
- Untrimmed Input
- Number vs. Date vs. Character and leading single quote (')
- Re-entry issue and using application.enableevents = false
Range Names- Ctrl+F3 - Range Name Dialog
- Range("range_name").method
- [range_name] - gets value from range name
Application Object- .Calculate - recalculates spreadsheet
- .StatusBar = "Processing " & j & " of " & lTotal
- .ScreenUpdating - turn off screen painting for optimizing
- .DisplayAlerts - Don't display Dialog like when deleting a sheet
- .Evaluate("=excel function and parameters")
- .EnableEvents - Prevent workbook, worksheet or other events from executing
- .CutCopyMode = False
- .IgnoreRemoteRequests = True ' prevents excel code from external source from opening in this window
- .Left
- .Top
- .UserName
Workbooks- Activeworkbook.
- Activeworkbook.Close
- Workbooks.Count
- Workbooks(sCaption ).Activate
- Workbooks.Open sFile, False, True
- Activeworkbook.name
- Windows(caption).Activate
|
