FormulaArray inside function

  • Thread starter Thread starter stickman373
  • Start date Start date
S

stickman373

Function test(drybulbtemp As Double, percentrelativehumidity As Double)

Selection.FormulaArray = "=INDEX(C[-4],MATCH(drybulbtemp
percentrelativehumidity,Dry_Bulb_Temp&Percent_Relating_Humidity,0))"

test = Selection.FormulaArray

End Function


what i am trying to do is create a function where you say the cell is:

=test(1,2)

and then it will run what I have above plugging in the values 1 and
into the function I have above and it will then output the value in th
same cell, but this isnt working, I know its not the way to do it, bu
i am new to vba and need this to work.

Currently this function just puts =test(1,2) in the cell :
 
Hi
not possible with formulas. Formulas used from a worksheet can't change
other cells. They can just return a value
 
hey, I'm not trying to change other cells, i'm saying that the cell tha
the function entered in, I want it to return the value of thi
formula:

"=INDEX(C[-4],MATCH(drybulbtemp
percentrelativehumidity,Dry_Bulb_Temp&Percent_Relating_Humidity,0))"

where drybulbtemp =1 and percentrelativehumidity = 2 are taken from th
function:

=test(1,2)

do you know what I mean
 
Hi
yes but you were trying to insert this formula in a cell (the current
selected) and this is not possible.
Just as another question: Why use VBA at all for this.<Why not a simple
worksheet function directly in your spreadsheet
 
yes I did insert it directly into the spreadsheet as a formula to start
but drybulbtemp & percentrelativehumidity are going to change and
want a way to easily have those values change without editing th
formula because i am not the only one who will be using the workshee
and chances are it would confuse other people when they would want t
change those values.

do you know a way in which I can accomplish something like this
 
Worksheet functions can only return values to their calling cells. They
can't change other cells' values, including entering functions. You'll
have to implement the function entirely in VBA.

If I understand what you're trying to do correctly, this may work for
you:

Public Function test1(drybulbtemp As Double, _
percentrelativehumidity As Double) As Variant
Dim vDBT As Variant
Dim vPRH As Variant
Dim i As Long
With Application.Caller
If .Column < 5 Then
test1 = CVErr(xlErrRef)
Else
vDBT = Range("Dry_Bulb_Temp").Value
vPRH = Range("Percent_Relating_Humidity").Value
If UBound(vDBT, 1) <> UBound(vPRH, 1) Then
test1 = CVErr(xlErrRef)
Else
test1 = CVErr(xlErrNA)
For i = LBound(vDBT) To UBound(vDBT)
If vDBT(i, 1) = drybulbtemp Then
If vPRH(i, 1) = percentrelativehumidity Then
test1 = .Parent.Cells(i, .Column - 4)
Exit For
End If
End If
Next i
End If
End If
End With
End Function
 
Hi
put these values also in some cells and use cell references to access
these values
 
Stickman,

The function can return a value, but it doesn't actually become the contents
of the cell. That would destroy the call to the formula, which must remain
in the cell. The function call, =test1(1,2), remains in the cell, but the
value returned from the function appears in the cell (or wherever the call
appears, as in =Test1(1, 2) + Test2(3, 4 -- In this case Test1(1, 2) is
equivalent to whatever the function returns). Functions can't change any
cell (to do so would violate causality constraints of the universe, and we
might disappear or something).
 
wow JE McGimpsey that does work, thank you so much it saved me.

and thanks to both of you for trying to help m
 
Back
Top