View Full Version: Excel VBA Handy Code

Excel VBA Handy Code topics

• Save all sheets as new book - no macros
• Printer - show printer properties
• Chart - add embedded chart via VBA
• UDFs for NETWORKDAYS and EOMONTH (VBA)
• Remove all VBA code and objects
• Hard Drive Serial Number (get it)
• SpecialCells functionality
• Convert entire column to Proper case
• Custom filter - multi criteria
• Replace formula with value on entry
• Hide some sheets - unhide others
• Delete Duplicate Rows
• Operating system folder workaround
• Parsing in Excel
• Sorting - more than 3 Keys
• Superfast sorted unique list
• Spellcheck using UK English
• Protection: Workbook password cracker
• Protection: Sheet password cracker
• VBE - Copy code to clipboard
• Pattern matching using "Like"
• Exclude numbers from text entry
• Add UDF description to function wizard
• Worksheet sort
• Opening workbooks with VBA - functions
• Custom BeforeClose/BeforeSave dialog
• Sheets - copy sheet array, save as new book
• Worksheet test
• Only show one sheet at a time (hide all others)
• Email active workbook as attachment with message
• Drives - Get Hard-drive serial number
• Security - Workbook only opens on one PC
• VBE "Copy and Paste"
• AddIn - Auto Install an .xls file as an add-in
• Self-destructing Workbook
• Protection: Delete all VBA if VBA password cracked
• InputBox Cancel button
• Number Rows
• Bug Report
• Select Case allied with 'Like' operator
• Security - Trust access to visual basic project
• Paths - default
• FileExists function
• SheetExists/ChartExists functions
• Dialogs - excel
• Install Template automatically
• Last row/Next row - finding last & selecting next
• Lock/Unlock VBA Project programmatically
• Email active workbook as attachment
• Drives - get info
• Email active sheet Range as body of mail - Call
• Email active sheet as body of mail - Call + msg
• Email active sheet as body of mail in PERSONAL.xls
• Email active sheet as body of mail - Call + msg
• Email active sheet as body of mail - Call example
• VBE - Add new Help control to menu
• VBE tools - actuate programmatically
• Links - delete or list external links in formulas
• Code modules - Removing
• Code modules - Exporting
• Code modules - Importing
• Links - delete all
• Code as you go (take two)
• WordArt - add, fix size and position on sheet
• UserForm chart
• Manipulating images - jigsaw
• Export pictures from Excel
• ID a shape using right-click menus
• ID all worksheet shapes (inc pictures)
• VBE window - Open/Close programmatically
• Event code - add it programmatically
• Email sheet, assign code to buttons on sheet
• GUID - add/remove reference programmatically
• Workbook name with no .xls on end...
• Run procedure in another workbook
• Copy cell formulas - (no Copy/Paste)
• Special cells
• Calendar control - standard calendar
• ControlTipText for worksheet objects
• Multi-column list boxes
• ID OLE objects - get ID
• Rounding a number to the nearest half
• Error message - generating some
• Clean up text
• Zoom/Unzoom all sheets
• Use cell values to call procedures
• Unique items (collection)
• Find Function
• Special Symbols
• Sheet Activate Dialog
• Search FOR wildcard characters (2 methods)
• Search closed workbooks for VBA code
• Save a password in a text box
• Restrict action to a given range
• Restrict action to a given Column or Row
• Replace wildcard chars * and ? on s/sheet
• Replace pilcrows after pasting to text box
• Sheets codename - reference it in loop
• Range multi-select (example)
• Put like data on different sheets
• Arrays - put a data column into an array (e.g.)
• Pause Function
• Password property example
• Open all books in folder with similar names
• Move or copy folders
• Move file or folder
• Monitor Metrics (API)
• List all open workbooks
• Iterate through all possibilities
• Hide & unhide columns (except some cells)
• Drives - get hard drive letter
• GUID references (list them)
• Gosub and Return - use of
• GetOpenFilename demo
• VB project - get it
• Column Letter - get it
• Get Data from Closed Book
• Find file function
• Excels Random function - how random is it?
• Excels Camera function - using it
• Email selected sheets as attachment with message
• Email selected sheets as attachment
• Email active workbook as attachment with message
• Email active workbook as attachment
• Email active sheet as attachment with message
• Email active sheet as attachment
• Double-click a cell (SendKeys)
• Double-click a cell (API)
• Cross reference function
• Count number of data cells
• VBA code lines - count number of lines in project
• Arrays - count items in an array
• Count & list all files on desktop
• Copy colours from another book
• Conditional format some cells in a row
• ID Command bars
• Colour indexes - list (with examples)
• Clipboard - clear items
• BuiltinDocumentProperties - change
• Attributes - change (all workbooks in folder)
• Windows search dialog - call (API)
• Arrays - access an element of an array
• About dialog (API)