VBA – Log book – keep updating

Jul 13 

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

Jul 14

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

Notes

  • 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 …

=IF(ISTEXT(A3),IF(LEN(A3)=9,DATE(RIGHT(A3,4),LEFT(A3,1),RIGHT(LEFT(A3,4),2)), IF(LEN(A3)=10,DATE(RIGHT(A3,4),LEFT(A3,2),RIGHT(LEFT(A3,5),2)),”Error”)),DATE(YEAR(A3),DAY(A3),MONTH(A3)))

Jul 15 2017

Error: Object variable or With block variable not set

Error name:

the error is because I defined another sub named “selection” which conflicts with the Selection method ……. ~!!!!!

https://stackoverflow.com/questions/13635768/excel-vba-selection-copy-no-longer-works

— 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

Jul 17

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#

Jul 18

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.

Jul 31

Excel relative, absolute and mixed references  – very helpful

https://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

$A$1 vs A1 —

Aug 1

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.

Aug 2

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….
Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *