Variable Variables and Sums of Them in Excel VBA -

to alleviate summer boredom, decided try make excel program keeps track of how money owe based on properties owned , number of houses on properties in board game monopoly (some new rules i'm experimenting with). set spreadsheet such every property in own row. want excel program this:

let's you're looking @ cell. then, i=1 10 (since doubt more 10 players in monopoly feasible), if value of cell right i, add value of cell you're looking @ sum player i.

can generate variables variables sum1, sum2, sum3, ... , sum10? , can like

sum'i' = sum'i' + cell.value

within loop if condition in paragraph 2 met?

update: have tried code, , works in getting player 1's sum. however, gives player 1's sum players, , it's cumbersome update whenever buys new property or upgrades current property (i need re-type formula each update).

public function monopolysum(i integer) variant     dim sum(1 10) variant, rng range, cell range      = 1 10         sum(i) = 0     next      set rng = range("c2:c29")      each cell in rng         = 1 10             if cell.offset(0, 1).value =                 sum(i) = sum(i) + cell.value             end if         next     next cell      = 1 10         monopolysum = sum     next  end function 

thanks in advance everyone! have little experience programming , appreciate guidance :)

you looking arrays:

dim varsum(1 10) variant 'access this: varsum(i) = xyz 

---

updated answer
see several improveable points:

1. you giving i parameter function, reassign values later. name parameter better iplayer (what assume needed for)
2. you want calculate sum specific player? dont need array , calculate sums players.
   edit (to honest: dont need vba this. take @ sumif)
3. you want calculate sum players, 1 big sum? have write monopolysum = sum(i) in last for-loop.
   edit (here use sum, think know that)
4. you give desired range parameter function, dont have set "hard" function. try public function monopolysum(iplayer integer, rnggame range) variant
5. the answer initial question correct? should accepted :)