VBA in Excel - Properties, Methods and Events - 1

       One of the best way to learn programing is to learn from the already written codes. As I start to learn VBA for Excel, I feel it extremely difficult to put together the arguments and form a clear knowledge base. The exercise of putting together the  myriad arguments and syntax for programming appears like solving a jigsaw  puzzle.

    In this Excel VBA series, I will posting my learnings of the commonly used properties, methods and events.

   Thanks to my gurus Professor Charlie Nuttelman of  University of collorado and Lokesh Jayasankar sir of BNP Paribas for being of kind guidance in helping me to learn.

    These are basically sample codings. Explanation is appended to codings wherever I felt they are necessary.

1)   msgbox range("d22")

2)   Range("a3:aa5000").AutoFilter Field:=5, Criteria1:="OS-sal"

3)   range("G:G").select

4)   sales = WorksheetFunction.Sum(Range("J:J"))

Range("a1") = sales

5) Displays the color index for the color in the cell

   msgbox range("A1").interior.colorindex

6)   Application.workbooks("Project6).Worksheets("Main").Range(B16")

7)   range("a3").offset(1000,1000)

8)   Copy data from one worksheet and paste into another worksheet

Worksheets("Commands").Range("A1").copy

Worksheets("Feuil3").select

Range("A1").select

Activesheet.paste

9)

Sub Example9( )

Dim DIV as double

Sheet1.Select

ActiveSheet.Range("A8:S60000").autofilter field:=5, Criteria1:="DIV"

With wksT.autofilter.Range

Range("J" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select

Range(Selection, Selection.End(xlDown)).Select

DIV = Application.WorksheetFunction.Sum(Selection)

End With

Range("P8:P" & Cells(Rows.Count, "B").End(xlUp).Row)

End sub

10)  msgbox(" the square is "&y&" only.")

11)  msgbox(" the square is "& formatnumber(y,2)&" only.")

12)  Get the value in active cell

Activecell = formatnumber (y,2)

13)   Get the value from cell A1 and output the value to cell C3

Input

x = range("a1")

Output

range("c3")=formatnumber(y,2)

​

14)  Msgbox range("b2:d3").count * activecell + cells(5,2)* inputbox("Please enter a number:")

15)  set w1=worksheets("sheet1)

16)  Pick a cell from existing selection

Selection. Cells(2,2)

17)  Count the number of rows and columns in a selection

nr= selection.rows.count

nc=selection.columns.count

18)  x = inputbox()

19)Get a value of 5 into a cell some rows/columns from activecells

  activecell.offset(2,2) = 5

20)  sub example20()

dim x ,y ,z

x=inputbox()

y=activecell

z= x+y

activecell.offset(2,2) = z

End sub