VBA Basics

How to select Cells

Range("A1:A10").Activate
Range("A1:A10").Select
Range("A1").Activate
Cells(10,1).Select
Range(Cells(1, 1), Cells(10, 1)).Select
[A1:A10].Select

Note:
Activate and Select is the same thing (as far as I understand)
Cells(10,1) is the same as Range("A10") and is only cell.
[] is the same as Range. Notice the range is without " ".


Write to a Cells

Cells(1,1).Value=10
Range("A2:A10").Value = 10
Range("A11").Formula = "=SUM(A1:A10)"

Selected cells
Selection.Value = 10
ActiveCell.Formula = "=SUM(A1:A10)"


Copy/paste
Range("A12").Value = Range("A11").Value
Range("A1:A11").Copy Range("B1")
Range("Sheet1!A1:A11").Copy Range("Sheet2!A1")


Sub EndOfRangeCopy()
Range("A1").Activate

Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select ' Rows.Count = 65536

Range("A1", ActiveCell.Address).Copy

End Sub



Help pages
Press F2. Search for a formula. We are now in something called the Object Browser (box icon)
Mark the found formula. Press F1 to find the help page, or right click and select help.
Close Search page (Object browser) by right click and select hide --> get you back to your module.
New finding: Double click on the word in code and press F1 will go directly to the help, or F2 to object browser.

Alt+F11 will toggle between VBA window and Excel window. In the beginning it can be a good idea to be working with 2 window halves, side by side.


Procedures: Function and Sub
Functions are used to write formula (my formulas) or UDF's=UserDefinedFuntions.
Sub's or Subroutines are everything else.

Functions pass values in and out:

Function ValueOut(ValueIn)
ValueOut = ValueIn*2
End Function

=ValueOut(100) --> 200


Where do I put the code?
To the left there is a list that looks like Windows Explorer. It is called Project Explorer (it should be called the VBA explorer if you ask me). You can reach it from the Flying windows button.
There is Module1
- if not you can add it by selecting from the menu: Insert Module or the Icon with the glare (it has 4 alternatives)
There is Sheet1...to x
There is ThisWorkbook

Normally all codes goes in Module1...to x (like sheet1...to x)
You can put code in sheet1 if you only (simply said) want it to run there.


How to get started
Best way is copy code from somewhere and test, test and test. It's like learning to ride a bike.
You can not learn to ride a bike by watching someone doing it, or reading about it or looking at a video. You have to do it yourself. No way around that - unless you are an exception and learned to swim that way.

For instance copy the code above/below and learn what works and what don't.
My way is to writing about it. Trying to teach someone.


How do I avoid using static ranges?
One way is to Name a range, in Excel.
That will let Excel take care of the problem.

Static: Range("A1:A10")
Dynamic: Range("myRange")
We have defined myRange in Excel as A1:A10


How to change the Cell Properties?
How to change Background color and font (two of many properties). In Excel you would say you want to 'Format Cells'.

Sub Changeproperties()
Range("A11").Interior.ColorIndex = 5
Cells(11, 1).Font.Name = "Courier"
End Sub

Note: Cells() don't have property ColorIndex. Range() do.

Sub Changeproperties()
With Cells(11, 1).Font
.Name = "Courier"
.Bold = True
End Sub

Sub Changeproperties()
Range("A11").Select
With Selection.Borders(xlEdgeTop)
' or Borderaround
.ColorIndex = xlAutomatic
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub


Write to cells
Fill all selected cells

Sub FillSequence()
Dim cell As Range
Dim i As Long
For Each cell In Selection
i = i + 1
cell.Value = i
Next cell
End Sub


VBA Shortcuts: :
Alt+F11, to get to the Visual Basic Editor
Ctrl+R, to bring up the Project Window (should be on leftside)
F7, brings up the Code
Ctrl+G, brings up the Immediate window


Stand on the sub name and
- press F5 to run you code
- press F8 to stepwise go through it you code





Excel shortcuts:

Alt+F8 (program function key 8) [Picture Alt+F8 -- Macro]

The F8 -- macro list does not show macros that have arguments, nor will you see User Defined Functions. You can however call a those macros with another macro, without (...) arguments.

The Function macros (UDF's) can be found under the fx button. All UDF's are found under the Category.


Your personal.xls file is where you store your own macros and User Defined Functions (UDF), that you want to be available from any workbook. For macros you only want available in a single workbook you would install macros in that workbook.




Intermediate Window
Can be used to test code.
Ctrl+G, brings up the Immediate window


Write for instance
?Cells(14,1).SpecialCells(xlLastCell).row --> reply 22


Camera tool

Sub ChangeShading()
With ActiveSheet.DrawingObjects("Picture 1")
For intWrk = 1 To 14
.Formula = "A" & intWrk
Application.Wait Now() + 0.0000007
Next intWrk
End With
End Sub

Note: "Picture 1" refers to the name of the Camera tool picture


Sources: David McRitchie (Join, Get Started), Xl-logic, Lacher