Trying to write a VBA macro. What am I doing wrong? 1

[ReeceB65]

OK What am I doing wrong here!
It keeps throwing a sub end/exit error


INSITUDENS1
'
'------------------------------------------------------------
Function INSITUDENS1()
On Error GoTo INSITUDENS1_Err

Forms![Density Test]![Result 1] = Forms![Density Test]!DENS1
Forms![Density Test]![Voids 1] = Forms![Density Test]!V1
Forms![Density Test]![Density Ratio 1] = Forms![Density Test]!DR1
Forms![Density Test]![Result 2] = Forms![Density Test]!DENS2
Forms![Density Test]![Voids 2] = Forms![Density Test]!V2
Forms![Density Test]![Density Ratio 2] = Forms![Density Test]!DR2
Forms![Density Test]![Result 3] = Forms![Density Test]!DENS3
Forms![Density Test]![Voids 3] = Forms![Density Test]!V3
Forms![Density Test]![Density Ratio 3] = Forms![Density Test]!DR3
Forms![Density Test]![Result 4] = Forms![Density Test]!DENS4
Forms![Density Test]![Voids 4] = Forms![Density Test]!V4
Forms![Density Test]![Density Ratio 4] = Forms![Density Test]!DR4
Forms![Density Test]![Result 5] = Forms![Density Test]!DENS5
Forms![Density Test]![Voids 5] = Forms![Density Test]!V5
Forms![Density Test]![Density Ratio 5] = Forms![Density Test]!DR5
Forms![Density Test]![Result 6] = Forms![Density Test]!DENS6
Forms![Density Test]![Voids 6] = Forms![Density Test]!V6
Forms![Density Test]![Density Ratio 6] = Forms![Density Test]!DR6
Forms![Density Test]![Result 7] = Forms![Density Test]!DENS7
Forms![Density Test]![Voids 7] = Forms![Density Test]!V7
Forms![Density Test]![Result 8] = Forms![Density Test]!DENS8
Forms![Density Test]![Voids 8] = Forms![Density Test]!V8
Forms![Density Test]![Result 9] = Forms![Density Test]!DENS9
Forms![Density Test]![Voids 9] = Forms![Density Test]!V9
Forms![Density Test]![Result 10] = Forms![Density Test]!DENS10
Forms![Density Test]![Voids 10] = Forms![Density Test]!V10

Private Sub AverageDens_AfterUpdate()
x = Forms![Density Test]![No of Test]
For n = 1 To x
Dat = Choose(n, Forms![Density Test]!DENS1, Forms![Density Test]!DENS2, Forms![Density Test]!DENS3, Forms![Density Test]!DENS4, Forms![Density Test]!DENS5, Forms![Density Test]!DENS5, Forms![Density Test]!DENS6, Forms![Density Test]!DENS7, Forms![Density Test]!DENS8, Forms![Density Test]!DENS9, Forms![Density Test]!DENS10)
S1 = S1 + Dat
Next

Forms![Density Test]![AvD] = S1 / x

Forms![Density Test]![Av Density] = Forms![Density Test]![AvD]

End Sub

INSITUDENS1_Exit:
Exit Sub

INSITUDENS1_Err:
MsgBox Error$
Resume INSITUDENS1_Exit

End Sub

 

[ReeceB65]

So I tried this and now I still get a compile error
Exit sub not allowed in Function or Property
This was converted from a Macro

'------------------------------------------------------------
' INSITUDENS1
'
'------------------------------------------------------------
Function INSITUDENS1()
On Error GoTo INSITUDENS1_Err

Forms![Density Test]![Result 1] = Forms![Density Test]!DENS1
Forms![Density Test]![Voids 1] = Forms![Density Test]!V1
Forms![Density Test]![Density Ratio 1] = Forms![Density Test]!DR1
Forms![Density Test]![Result 2] = Forms![Density Test]!DENS2
Forms![Density Test]![Voids 2] = Forms![Density Test]!V2
Forms![Density Test]![Density Ratio 2] = Forms![Density Test]!DR2
Forms![Density Test]![Result 3] = Forms![Density Test]!DENS3
Forms![Density Test]![Voids 3] = Forms![Density Test]!V3
Forms![Density Test]![Density Ratio 3] = Forms![Density Test]!DR3
Forms![Density Test]![Result 4] = Forms![Density Test]!DENS4
Forms![Density Test]![Voids 4] = Forms![Density Test]!V4
Forms![Density Test]![Density Ratio 4] = Forms![Density Test]!DR4
Forms![Density Test]![Result 5] = Forms![Density Test]!DENS5
Forms![Density Test]![Voids 5] = Forms![Density Test]!V5
Forms![Density Test]![Density Ratio 5] = Forms![Density Test]!DR5
Forms![Density Test]![Result 6] = Forms![Density Test]!DENS6
Forms![Density Test]![Voids 6] = Forms![Density Test]!V6
Forms![Density Test]![Density Ratio 6] = Forms![Density Test]!DR6
Forms![Density Test]![Result 7] = Forms![Density Test]!DENS7
Forms![Density Test]![Voids 7] = Forms![Density Test]!V7
Forms![Density Test]![Result 8] = Forms![Density Test]!DENS8
Forms![Density Test]![Voids 8] = Forms![Density Test]!V8
Forms![Density Test]![Result 9] = Forms![Density Test]!DENS9
Forms![Density Test]![Voids 9] = Forms![Density Test]!V9
Forms![Density Test]![Result 10] = Forms![Density Test]!DENS10
Forms![Density Test]![Voids 10] = Forms![Density Test]!V10
x = Forms![Density Test]![No of Test]
For n = 1 To x
Dat = Choose(n, Forms![Density Test]!DENS1, Forms![Density Test]!DENS2, Forms![Density Test]!DENS3, Forms![Density Test]!DENS4, Forms![Density Test]!DENS5, Forms![Density Test]!DENS5, Forms![Density Test]!DENS6, Forms![Density Test]!DENS7, Forms![Density Test]!DENS8, Forms![Density Test]!DENS9, Forms![Density Test]!DENS10)
S1 = S1 + Dat
Next
Forms![Density Test]![AvD] = S1 / x
Forms![Density Test]![Av Density] = Forms![Density Test]![AvD]

INSITUDENS1_Exit:
Exit Sub

INSITUDENS1_Err:
MsgBox Error$
Resume INSITUDENS1_Exit

End Sub

 

[combo]

You have [tt]Function INSITUDENS1()[/tt], instead of [tt]Sub[/tt], you need [tt]Function[/tt] after [tt]Exit/End[/tt].

VBA IDE changes this automatically if the syntax is proper, i.e. having initially [tt]Sub INSITUDENS1()[/tt] in the code and changing Sub=>Function here, the above changes are automatic.

combo

 

[ReeceB65]

Awesome!
Thanks Combo.
Got the VBA to run and close on click

However now using the script

x = Forms![Density Test]![No of Test]
For n = 1 To x

Dat = Choose(n, Forms![Density Test]!DENS1, Forms![Density Test]!DENS2, Forms![Density Test]!DENS3, Forms![Density Test]!DENS4, Forms![Density Test]!DENS5, Forms![Density Test]!DENS5, Forms![Density Test]!DENS6, Forms![Density Test]!DENS7, Forms![Density Test]!DENS8, Forms![Density Test]!DENS9, Forms![Density Test]!DENS10)

S1 = S1 + Dat

Next
Forms![Density Test]![AvD] = S1 / x

Forms![Density Test]![Av Density] = Forms![Density Test]![AvD]

I get an answer of an average that is 2.334 when in reality the average is 2.327 from 6 values of 2.333, 2.287, 2.365, 2.264, 2.377, and 2.339.

Any idea why?
Is this code wrong?

TIA Reece

 

[ReeceB65]

Managed to get my code working

I'd like to share how I did this

Be mindful that x was a varying number of fields from 3 to 6.
x was stated as number of samples on my form.
"x = Forms![Density Test]![No of Test]"
So had to be able to calculate from whatever number of fields was required.

'Average Density Ratio
For q = 1 To x
Dat4 = Choose(q, Forms![Density Test]!DR1, Forms![Density Test]!DR2, Forms![Density Test]!DR3, Forms![Density Test]!DR4, Forms![Density Test]!DR5, Forms![Density Test]!DR6)
S4 = S4 + Dat4
Next
Forms![Density Test]!MDR = S4 / x
MDR = S4 / x

'Charact Density Ratio
For t = 1 To x
Dat5 = Choose(t, Forms![Density Test]!DR1, Forms![Density Test]!DR2, Forms![Density Test]!DR3, Forms![Density Test]!DR4, Forms![Density Test]!DR5, Forms![Density Test]!DR6)
S5 = S5 + (Dat5 - MDR) ^ 2
Next
DRSD = Sqr(S5 / (x - 1))
Forms![Density Test]!CDR = MDR - (Forms![Density Test]!K * DRSD)

K is a factor of risk applied to the std dev. THe base calc is Characteristic DR = Av DR-(0.92*stddev of DR)
The density ratios are across 6 different fields in a table and on a form.

Thanks for the help here on this site.
Really appreciated!