Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think it should be

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

    The Public Function was not getting the parameter
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks - that worked, and simple and why didn't i think of that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •