VBA tips

This post introduces some tips in writing efficient VBA programs, as well as code for certain common tasks.

Tasks:

  1. 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

2. Change date formats in Excel spreadsheet:
=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)))

Other tips with using dates:

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

3. Find last used row and column and clear all formats

  • selection.ClearContents
  • selection.ClearFormats

4. Copy contents from one sheet to another.

Sub Simple_Copy_Paste() Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Range("B2") End Sub

5. Count the number of non-empty cells !

MsgBox LoopIndex & " " & CellCount

6. Work 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

7. Change number formatting…

paste_ws.Range("J3:J" & LastRow).NumberFormat = "0.000000"

8. How to format cells ?

9. Replace blank values with zeros..
paste_ws.Range("K3:K" & row_length).Replace "", 0, xlWhole

Tips 

  1. What’s the difference between a VBA sub, vs a VBA function?
    1. function return values, but subs do not
    2. functions can only be called in other procedures, but subs can be executed directly

Reference here: http://analystcave.com/vba-function-vs-vba-sub-procedures/

2. Avoid avoid select method (because it is slow and error-prone) using VBA

A stackoverflow answer: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros#

3. Best practices for debugging

  • Examining the code
  • Inserting MsgBox functions at various locations in your code
  • Inserting Debug.Print statements
  • Using the Excel built-in debugging tools

4. The scope of VBA variable …

  • local — within sub
  • module — at the beginning of module
  • public scope — every module

5. Be careful when output to csv using VBA! Always alert. 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.

6. Handling errors in VBA. VBA Error handling ..

7. Excel relative, absolute and mixed references  – very helpful to know.

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

Examole: $A$1 vs A1

8. Identification a variable before calling: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/error-messages/object-variable-or-with-block-variable-not-set

9. Do not name a variable as the same name as a method. e.g. I once defined a sub named “selection” which conflicts with the Selection method. The error was Error: Object variable or With block variable not set.

References 

Book: VBA for dummies. A quick guide for knowing the structure of the language. Suggested reading time = 2 hours.

Book : IBM Excel spreadsheet modeling best practices. An old book published in 1999 …

 

Share this post

Leave a Reply

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