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

TED Talk by Sam Berns

Sampson Gordon "Sam" Berns (October 23, 1996 – January 10, 2014) was an American teen who had progeria and helped raise awareness about the disease. He was the subject of the HBO documentary Life According to Sam, which was first screened in January 2013. He died one year later, after appearing in a TEDx Talks video titled "My philosophy for a happy life."

Be Ok with what you can't do (now), because there is so much you can do (now)

  

Wise Words - 2

My thought: And let the goal be selfless

GST Yathra- 18- Time of Supply of Goods

Section 12- Time Of Supply Of Goods

Sub-section 2- Forward charge cases

1. The time of supply of goods shall be the earlier of the following dates:

a) Date of issue of invoice by the supplier or the last date for issuance of the invoice as per Section 31

b) Date of receipt of payment

1. However, where the supplier receives an amount up to one thousand rupees in excess of the amount indicated in the invoice, to the extent of such payment the time of supply shall be the date of issue of invoice at the option of the said supplier.
2. The Date of receipt of payment shall be the

a) date on which the payment is entered in his books of account or
 

b) date on which the payment is credited to his bank account, whichever is earlier

Sub-section 3- Reverse charge cases

1. Time of supply shall be the earliest of the following dates:

a) date of the receipt of the goods or

b) date of payment as entered in the books of account of the recipient or the date on which the payment is debited in his bank account, whichever is earlier or

c) date immediately following thirty days from the date of issue of invoice or any other document, by whatever name called, in lieu thereof by the supplier

      2. Where it is not possible to determine the time of supply as above, the time of supply shall be the date of entry in the books of account of the recipient of supply

Sub-section 4- Where goods are obtained through redeeming a voucher

1. Date of issue of voucher, if the supply is identifiable at that point
2. The date of redemption of voucher, in all other cases.

Sub-section 5- Other cases

In a case where a periodical return has to be filed

The date on which such return is to filed.

In other cases

The date on which the tax is paid

Sub-section 6- In relation to an addition in the value of supply by way of interest, late fee or penalty for delayed payment

Date on which the supplier receives such addition in value.

Section 31- Date of issuance of invoice

1. Invoice shall be issued:

In case of supply involves movement of goods

Before or at the time of the removal of goods

In any other case

Before or at the time of making available the goods

2. Invoice shall mention the description, quantity and value of goods, the tax charged thereon and such other particulars as may be prescribed
3. In case of continuous supply of goods, where successive statements of accounts or successive payments are involved, the invoice shall be issued before or at the time each such statement is issued or each such payment is received.
4. Notwithstanding anything contained in sub-section (1), where the goods being sent or taken on approval for sale or return are removed before the supply takes place, the invoice shall be issued before or at the time of supply or six months from the date of removal, whichever is earlier.

Wise Words -1

The Ever Genrous Devah...

Maithreem Bhajatha , Akhila Hrujjethreem,

Atmavadeva paraanapi pashyatha

Jananee Pruthivee Kaamadughaastey

JanakO Devah Sakala Dayaaluh

Sreyo Bhooyaath Sakala Janaanaam 

Maithreem Bhajatha Akila Hrith Jeththreem

Atmavat Eva Paraan api pashyata

Yuddham Tyajata , Spardhaam Tyajata, Tyajata Pareshwa akrama akramanam

Jananee Prthivee Kaamadughaastey

JanakO Deva: Sakala Dayaalu

Daamyata Datta Dayathvam Janathaa

Sreyo Bhooyaath Sakala Janaanaam

Sreyo Bhooyaath Sakala Janaanaam

Sreyo Bhooyaath Sakala Janaanaam. With friendship please serve,

And conquer all the hearts,

Please think that others are like you,

Please forsake war for ever,

Please forsake competition for ever,

Please forsake force to get, Some one else’s property,

For mother earth is a wish giving animal,

And God our father is most merciful,

Restrain, donate and be kind,

To all the people of this world.

Let all the people, live with bliss,

Let all the people live with bliss,

Let all the people live with bliss.

Budget 2018 - Impact Points for Common Man

General

• Education cess of 3% has been replaced by the Health and Education cess of 4%.
• Henceforth, 40% of the sum withdrawn from NPS by senior citizens is exempt non-salaried assessees too. Hitherto it was exempt only for salaried assessees.

For Salaried persons

• For assessees with salary income, a standard deduction of Rs.40,000/- will be allowed
• Currently, Medical reimbursement and transport allowance is exempt upto Rs. 15,000 and  Rs.19,200 respectively.  This has been replaced with introduction of Standard deduction.

For Senior citizens

• Mediclaim deduction has been increased from Rs.30,000 to Rs.50,000
• For ailments specified in Section 80DDB, the deduction limit in respect  treatment for senior citizens and very senior citizens is Rs. 100,000/-
• In respect of interest earned from Savings bank account, Fixed deposit account and Post office deposits accounts a deduction of Rs.50,000/-  would be allowed. However, no deduction under section 80TTA shall be allowed for such assessees.
• No TDS will be deducted on interest income for senior citizens upto Rs. 50,000/-

For Capital asset holders

• Equity shares and units of equity oriented funds held for more than 12 months are taxable at 10% on sales. No indexation benefit will be given in respect of such sales
• Exemption in respect of capital gains through investing in NHAI or REC bonds will be available only if the bonds are redeemable after 5 years (as against 3 years)