Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problems
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 he 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problems
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. -- Regards, Tom Ogilvy "penguat" wrote: 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 he 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problems
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" wrote: 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. -- Regards, Tom Ogilvy "penguat" wrote: 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 he 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problems
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,D4:D12) D2: Sample size of new sample D4:D12: Quantity in each species for the current sample. -- Regards, Tom Ogilvy "penguat" wrote: 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" wrote: 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. -- Regards, Tom Ogilvy "penguat" wrote: 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 he 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
Indirect and Sumif Syntax Problems | Excel Discussion (Misc queries) | |||
copy and paste between xls file syntax problems | Excel Programming | |||
Problems merging an excel file due to code or file problems? | Excel Programming |