Error Handling – https://trumpexcel.com/vba-error-handling/
More Error handing – “Resume” as apposed to On Error Resume Next – https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/resume-statement
error codes – https://onlinelibrary.wiley.com/doi/pdf/10.1002/9781118257616.app3
.VALUE vs .VALUE2 vs .TEXT
Source – https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/
.TEXT gets the formatted value of a cell. Actually what it looks like. So if the column with is too small and the numbers don’t fit then it will return “#####”. This also is the slowest in retrieving the contents of the cell than .VALUE or .VALUE2
.VALUE can damage numbers. If the cell is formatted as a date or currency then Range.Value converts the underlying double-precision value into a VBA date or currency variable before passing it to the VBA variable used in the assignment statement. Since VBA currency only has 4 decimal places this can cause a loss of accuracy. Then when you assign the VBA currency or Date variable to a cell using .Value, it rounds the value to 2 decimal places regardless of cell formatting. ALSO There is a problem using .Value to get a date and passing it to VLOOKUP.
.VALUE benefit – Using .Value to retrieve cells formatted as dates into a variant is useful because then you can use IsDate() to detect that the cell was a date.
.VALUE2 – This works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And that’s probably why its faster than .Value when retrieving numbers.
Writing VBA code to access things outside of Excel
This is not explained at most websites, that to write certain types of VBA code requires you to turn on a reference to that Library of special coding. For example, to be able to write VBA code to communicate with external programs like Adobe, Apple QuickTime, Crystal Reports, Dropbox and more, you need to link a library of code to the VBA editor. But this doesn’t only apply to external programs! If you want to write code to automate the importing and exporting VBA Modules in Excel, you need to do the following 2 things in Excel: 1) in the Excel VBA editor click Tools, References, and put a check in “Microsoft Visual Basic for Applications Extensibility 5.3”, and 2) in the main Excel window press ALT-T-M-S and put a check in “Trust access to the VBA project object model”.
This is information on Microsoft Visual Basic for Applications Extensibility 5.3 https://bettersolutions.com/vba/visual-basic-editor/extensibility.htm
Ranges & Cells
The Complete Guide to Ranges and Cells in Excel VBA
Find the Last Row, Column, or Cell on a Sheet
VBA Tutorial: Find the Last Row, Column, or Cell on a Sheet
Import vba modules and forms
https://www.rondebruin.nl/win/s9/win002.htm
How to select range of cells
How to copy Data
Excel VBA Copy – The Complete Guide to Copying and Filtering Data
Scheduling
https://wellsr.com/vba/2019/excel/vba-application-ontime-to-schedule-macros/
https://www.snb-vba.eu/VBA_Application.OnTime_en.html
User Forms
VBA UserForm – A Guide for Everyone
Sorting
Range(“A1:C12”).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo
Range is the data that you want to sort
Key1 is the is the column or row that you want to sort on
Named Range – If you are using a Named Range that you can substitute it in the Range like so:
Range(“Nurses”).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlYes
Sort based on more than one column
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A1"), Order:=xlAscending
.SortFields.Add Key:=Range("B1"), Order:=xlAscending
.SetRange Range("A1:C12")
.Header = xlYes
.Apply
End With
TIP – if the range changes you can use this to solve that problem.
Range(“A1”, Range(“A1”).End(xlDown)).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo
Difference between worksheet.select and worksheet.activate
- Select can be used to select multiple objects (sheets, ranges, shapes, etc.) at the same time.
- Activate can be used to active one object within the selection.
Delete Row
Range(“A1”).EntireRow.Delete
Rante("A1:A3").EntireRow.Delete
What the above statement will do that is it will delete the row for the given row. For example, if we write Range(“A1”).EntireRow.Delete then the first row will be deleted as cell A1 belongs to the first row.
Or
Selection.EntireRow.Delete
Delete delete based on selection
OR
Rows(1).delete
Rows("1:3").delete
OR
Rows(1).EntireRow.Delete
Array complete guide
Excel VBA Array – The Complete Guide
Checkbox – Form Control
Link Check Boxes to Cells With a Macro
Overview of forms, Form controls, and ActiveX controls on a worksheet
Excel Form Control vs. ActiveX Control
Key Differences in Excel: Form Control Vs. ActiveX Control
Excel difference between Form Controls vs ActiveX Controls
Excel difference between Form Controls vs ActiveX Controls
Checkbox – ActiveX Control
https://www.automateexcel.com/vba/checkbox/
Option Button
https://www.automateexcel.com/vba/option-button-excel/
ListBox
https://www.thespreadsheetguru.com/blog/vba-useform-listbox-control-guide
ComboBox
https://www.contextures.com/excel-vba-combobox-lists.html
Protect a Worksheet
https://support.microsoft.com/en-us/office/protect-a-worksheet-3179efdb-1285-4d49-a9c3-f4ca36276de6
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect
How to change data using VBA on protected Cell
You need to use the Userinterfaceonly attribute with the Worksheet.Protect command like so: Worksheet.Protect “1234”, userinterfaceonly:=True
This needs to be run every time the Excel file is opened
PC Paramedic-Computer Log 