Syntax problems

[penguat]

The help is being particularly unhelpful: I am a very inexperienced
programmer who's not worked with VB before, and I'm trying to use it to do a
stat function which I can't do any other way. essentially my code is here:

Sub StatVariance()
Worksheets("Sheet5").Cell(4, 1).Value = 3
Sum = 0
Count = 1
Dim bign As Integer
bign = Sheets("Sheet3").Cells(58, 4)
Worksheets("Sheet5").Cell(2, 2).Value = bign
Dim n As Integer
n = Sheets("Sheet1").Cells(1, 6)
Worksheets("Sheet5").Cell(1, 2).Value = n
For j = 2 To Value = Worksheets("Sheet3").Cells(61, 5)
For i = 1 To j
Worksheets("Sheet5").Cell(3, (Count + 1)).Value = Sum
Ni = 2
Nj = 2
Sum = Sum + (WorksheetFunction.Combin((bign - Ni - Nj), n) /
WorksheetFunction.Combin(bign, n))
Count = Count + 1
Next i
Next j
Worksheets("Sheet1").Cell(2, 7).Value = Sum * 2
End Sub

what's wrong with it? what I'm trying to do is a sum of sums, that bit
should be fine, it's the introductory syntax I'm struggling with: how to
define a variable and set it to 0, and I'm not sure how to even tell it that
it's a program and should run when someone changes the contents of a cell,
for example.

 

[Tom Ogilvy]

It sounds like you want to place a formula in a worksheet cell like:

=StatVariance(Sheet5!A1,Sheet1!B9,Sheet5!F7,Sheet5!C5)

the in a general module you would put in a function similar to this:

Public Function StatVariance(rng1 As Range, _
rng2 As Range, rng3 As Range, rng4 As Range)
' doubles, singles, longs, integers will
' initialize to zero automaticall
Dim sum As Double, count As Long
Dim bign As Long
Dim n As Long
bign = rng4
count = 1
For j = 2 To rng1.Value
For i = 1 To rng2.Value
Ni = 2
Nj = 2
n = rng3.offset(count,0)
sum = sum + _
WorksheetFunction.Combin((bign - Ni - Nj), n) _
/ WorksheetFunction.Combin(bign, n)
count = count + 1
Next i
Next j
' now assign a result to the function name to be returned
' to the cell where the function is used
StatVariance = sum * 2
End Function

You code as written was setting values to cells all over two sheets - this
isn't allowed in a function used in a worksheet - it can use values from all
over, but it can only return a value to the cell in which it is written -
just like the builtin functions such as sum and stdev. The code above
certainly doesn't perform whatever functionality your original code performed
because I have no idea what you were trying to do - it is just pseudo code to
represent a few concepts.

 

[penguat]

I was trying to put the formulae under "rarefaction" in
http://folk.uio.no/ohammer/past/univar.html into excel so that I could use
them.

 

[Tom Ogilvy]

Public Function ExpectedSpecies(SampleSize As Range, _
RangeOfNi As Range)
Dim sum As Double, n As Double
Dim sSize As Double
If SampleSize.count > 1 Then
ExpectedSpecies = CVErr(xlErrRef)
Exit Function
End If
n = Application.sum(RangeOfNi)
sSize = SampleSize.Value
if n < sSize then
ExpectedSpecies = CVErr(xlErrNum)
exit Function
End if

For Each cell In RangeOfNi
sum = sum + (1 - Application.Combin(n, n - cell) _
/ Application.Combin(n, sSize))
Next
ExpectedSpecies = sum
End Function

Usage
=ExpectedSpecies(D2,D412)

D2: Sample size of new sample

D412: Quantity in each species for the current sample.