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

T2Do.com

 


VBA 1 Page

edit | random
What Can Excel Do (WCXD)?
Highly Used routines/functions
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


Frustrated? Tell us why?


$0.99 for a limited time at Amazon.com
Going Fast.

   
Home | About | Feedback| Privacy | Terms of Service | @alecberg | FAQ | | Copyright © 2022 All Rights Reserved. Edit
This site uses cookies if you don't want cookies and who doesn't want them as they're yummy, then leave.