How can I use the VBA-Excel function NORMINV with variables?

[stanhilliard]

The following works in VBA when the three parameters (0.05,0,1) are literal constants.

(1) Worksheets("Data").Cells(I, 3).Value = "=-NORMINV(0.05,0,1)"

However, the following fails when the three parameters are variables. I want to be able to change the variables in a loop. This fails:

(2) Worksheets("Data").Cells(I, 3).Value = "=-NORMINV(pp,Mean,SD)"

I tried to convert the three numeric variables to strings but the result still fails. It adds the formula to the cell, but the formula contains the literal names.

I got this to work in worksheet cells:

(3) =-NORMINV(RC[-1],RC[1],RC[2])

but I would like to work in VBA without referencing a worksheet at all. Is there a way to do that with both inputs and output being variables?

[p45cal]

pp = 0.05
Mean = 0
SD = 1
x = -Application.NormInv(pp, Mean, SD)
MsgBox x
'Worksheets("Data").Cells(I, 3).Value = x
'Worksheets("Data").Cells(I, 3).Value = -Application.NormInv(pp, Mean, SD)

[stanhilliard]

pp = 0.05
Mean = 0
SD = 1
x = -Application.NormInv(pp, Mean, SD)
MsgBox x
'Worksheets("Data").Cells(I, 3).Value = x
'Worksheets("Data").Cells(I, 3).Value = -Application.NormInv(pp, Mean, SD)

Thanks. That is exactly what I was looking for.

Another question:

Q: Is there an article or page that explains the system that applies?

[p45cal]

An internet search for the likes of worksheet functions in vba should find plenty of resources, including this one: https://docs.microsoft.com/en-us/office/vba/excel/Concepts/Events-WorksheetFunctions-Shapes/using-excel-worksheet-functions-in-visual-basic
See also: https://excelchamps.com/vba-worksheet-function/#ApplicationWorksheetFunction_vs_Application