VBA Swipefile

Worksheets

Worksheets("MySheet").Activate

ActiveSheet.Cells.Clear

ActiveSheet.Cells.EntireColumn.AutoFit

Application.WorksheetFunction.Average(Range("A1", Range("A1").End(xlDown)))

Range objects

Range("A1").Activate

Range("A1", Range("A1").End(xlDown))

nNumber = Range("MyCell").Value

nNumber = ActiveCell.Offset(0, 0).Value

Variables

Dim i As Integer

Dim nNumber As Integer/Long/Double

Dim sString As String

Dim oObject As Object/Variant

Vectors

Dim vVector(1 To 5) As Integer

Dim vVector2(1 To 5, 1 To 2) As Integer

For i = LBound(vVector) To UBound(vVector)
ActiveCell.Offset(i, 0).Value = vVector(i)
Next i

Creating a dictionary

' The module
' Remember to check the Scripting Runtime library

Option Explicit

Public Cars As Dictionary

Public Sub Main()

Dim myCar As Car
Set myCar = New Car
Set Cars = New Dictionary

Cars.Add "My Car", myCar

MsgBox (Cars.Item("My Car").Make)
MsgBox (Cars.Item("My Car").RetailPrice)

End Sub


' The class module

Public Make As String

Private Sub Class_Initialize()
Me.Make = "Vauxhall"
End Sub

Public Function RetailPrice() As Double
RetailPrice = 20000
End Function

Creating helper sheet for displaying variable content

Option Explicit

Public Sub mySubroutine1()

Dim variableName As String
Dim variable As String

variableName = "variable"
variable = "Hello world!"

Call Helper_Display(variableName, variable)

End Sub

Public Sub mySubroutine2()

Dim vectorName As String
Dim vector(1 To 2) As String

vectorName = "vector"
vector(1) = "Hello world!"
vector(2) = "Yes, indeed!"

Call Helper_Display(vectorName, vector)

End Sub

Public Function Helper_Display(ByVal name As String, ByVal content As Variant)

Dim flag As Boolean
flag = False

Dim ws As Worksheet

Dim helperSheet As String
helperSheet = name

For Each ws In ThisWorkbook.Sheets

If ws.name = helperSheet Then
flag = True
End If
Next ws

If flag = False Then
Worksheets.Add().name = helperSheet
End If

Worksheets(helperSheet).Activate
ActiveSheet.Cells.Clear
Range("A1").Activate

Dim i As Integer
Dim lb As Integer
Dim ub As Integer

Dim vectorTest As Boolean

vectorTest = IsArray(content)

If vectorTest = True Then

lb = LBound(content)
ub = UBound(content)

ub = ub - lb

For i = 0 To ub

ActiveCell.Offset(i, 0).value = i + lb
ActiveCell.Offset(i, 1).value = content(i + lb)
Next i

Else
ActiveCell.Offset(0, 0).value = content
End If

End Function