Find last row in a column. souce : https://www.rondebruin.nl/win/s9/win005.htm
Sub LastRowInOneColumn() 'Find the last used row in a Column: column A in this example Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With MsgBox LastRow End Sub
Book: VBA for dummies … goal = quick skim essential chapters. Know the structure of the language. Time = 2 hours max.
Book : IBM Excel spreadsheet modeling best practices 1999 …
Question: Sub vs function?
- function return values, but subs do not
- functions can only be called in other procedures, but subs can be executed directly
- objects in Excel —- think of objects in Python. Application.Workbooks(“Books1.xlsx”).Worksheets(“Sheets1”).Range(“A1”)
- property of objects —- value and address, Range object
A function to deal with changes of date formats in Excel …
Jul 15 2017
Error: Object variable or With block variable not set
the error is because I defined another sub named “selection” which conflicts with the Selection method ……. ~!!!!!
— identification = not defining variable before calling https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/error-messages/object-variable-or-with-block-variable-not-set
Error: catastrophic failure. The file is corrupted
- Find last used row and column and clear all formats
- How to avoid select method (because it is slow and error-prone)
Sub Simple_Copy_Paste() Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Range("B2") End Sub
How to debug …
- Examining the code
- Inserting MsgBox functions at various locations in your code
- Inserting Debug.Print statements
- Using the Excel built-in debugging tools
MsgBox LoopIndex & ” ” & CellCount
vbNewline inserts a line feedback ?
Working with Ranges —
- copying a range Range(“A1:A5”).Copy Range(“B1”)
- copying a variable size range
- selecting to the end of a row or column Range(ActiveCell, ActiveCell.End(xlDown)).Select
- selecting a row or column ActiveCell.EntireColumn.Select
A stackoverflow answer: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros#
Named ranges …
So VBA should be simple! Should take one day to learn. Why am I doing so slowly!!!! Angry with myself.
VBA variable scopes ..
- local — within sub
- module — at the beginning of module
- public scope — every module
VBA Error handling … The link is a nice tutorial.
How to format cells …
Change number formatting…
paste_ws.Range("J3:J" & LastRow).NumberFormat = "0.000000"
Replace blank values with zeros..
paste_ws.Range("K3:K" & row_length).Replace "", 0, xlWhole
<Some tips> What VBA does most of the time is selecting, copying and pasting…
When output to csv… always alert. Now this is not good. Closing this by force?
Note When using the SaveAs method for workbooks to overwrite an existing file, the Confirm Save As dialog box has a default of No, while the Yes response is selected by Excel when the DisplayAlerts property is set to False . The Yes response overwrites the existing file.When using the SaveAs method for workbooks to save a workbook that contains a Visual Basic for Applications (VBA) project in the Excel 5.0/95 file format, the Microsoft Excel dialog box has a default of Yes, while the Cancel response is selected by Excel when the DisplayAlerts property is set to False . You cannot save a workbook that contains a VBA project using the Excel 5.0/95 file format.
Excel relative, absolute and mixed references – very helpful
$A$1 vs A1 —
Use Excel to construct signals … Can only use VBA. Why? Because the conditions of for loop is changing. Thus, if in excel sheet, the length has to be fixed.
Excel dates are extremely annoying…
- Bloomberg downloads data into Excel — date problem. If < 12 then interpret as days
- Use python to clean data, use pd.read_csv(path, dayfirst = True)
- Then in Python the dates sequences are right. In excel, also right.
- But when output to csv, still wrong! It takes any number < 12 to be month….