Setting var in public function, get it in a sub

Topic

New Reply

I want to create a public variable that i can use in the project and i am not able to get the variable to return in the sub after call. I get things like “Argument not optional”. here’s the function:

Code:

Public Function Sampler(RC As Integer)

	MsgBox "RC is " & RC
		
	Sampler = (0.1 * (1 - 0.1)) / ((0.1 / (Excel.WorksheetFunction.NormSInv _
		(1 - ((1 - 0.9) / 2)))) ^ 2 + ((0.1 * (1 - 0.1)) / RC)) + 30
	
	MsgBox "SampleSize from function is " & Sampler

End Function

and this works:

Code:

	RC = myRS.Fields(0)
	
   
	Call Sampler(RC)

Meaning, I get the msgbox’s from the function with expected values. however, this

MsgBox “Sample Size is ” & Round(Sampler) & ” for population set ” & RC

bombs with “Argument not optional”.

Not sure what’s going on here. the sub that is calling is on a form (that is open) and I changed to to Public Sub and that didn’t help.

Reply | Quote

Replies

I think it should be

MsgBox “Sample Size is ” & Round(Sampler(RC)) & ” for population set ” & RC

The Public Function was not getting the parameter

Reply | Quote

thanks – that worked, and simple and why didn’t i think of that

Reply | Quote