I think this code  could help you

dim sum  as integer
dim i as integer
dim objSheet as worksheet
set objSheet=sheets("Sheet1") 'Sheet1 is worksheet Name
sum=0
for i=1 to 100
sum=objsheet.cells(i,2).value+sum
next
'Sum contains the final result

Let me know whether it worked fine or not