How to create excel function?

Question

I got an excel sheet to calculate some numbers excel sheet

I want to create an excel function that can change the value in H4:H7. Then capture the output in E9 and return the value.

I have tried this:

Public Function Isokinectic(number1 As Double)
Worksheets("Sheet2").Range("H4:H7").Value = number1
Isokinectic = Worksheets("Sheet2").Range("H9").Value
End Function

The purpose is that I got many numbers that need to pug into H4:H7 and return the value in H9.

Thanks!

A function cannot change the values on the sheet. You will need to make a sub routine to do that. — Subodh Tiwari sktneer, Apr 09 '17 at 09:15
Isokinectic = Worksheets("Sheet2").Range("H9").Value This should be Isokinectic = Worksheets("Sheet2").Range("E9").Value — JOHN, Apr 09 '17 at 11:40
@Variatus Well I am not aware of that if that is possible. Please have a look at MS documentation https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel — Subodh Tiwari sktneer, Apr 09 '17 at 12:10
@sktneer - there are "hacks" that allow a UDF to make changes to other cells - but they're not recommended for anyone who doesn't know what they are doing, so it is usually safest to just give a blanket statement of "a UDF cannot make changes to anything other than the calling cell(s)" as you did. — YowE3K, Apr 11 '17 at 07:58

score 1 · Accepted Answer · answered Apr 09 '17 at 10:29

1

What's the trouble you have with your function? In my test it did exactly what I expected the code to do. However, if you call it from the worksheet the argument number1 As Double would be wrong. You would call it like, =Isokinectic(A1) where A1 contains the number you want to process. But A1 is a range and your function would have to treat it as such.

Function Isokinectic(Source As Range)

    With Worksheets("Sheet2")
        .Range("H4:H7").Value = Val(Source)
        Isokinectic = .Range("H9").Value
    End With
End Function

Of course, this still leaves a question as to why you want to write the value in H9 somewhere (wherever you call your UDF). Trying to make sense of it, I arrive at this solution to be called from H7 = Isokinctic(H4).

Function Isokinectic(Source As Range)

    With Worksheets("Sheet2")
        .Range("H5:H6").Value = Val(Source)
        Isokinectic = Val(Source)
    End With
End Function

In this scenario you would enter the number in H4, the formula in H7 and get the range H5:H7 filled with the same number - Just thinking, lol:

answered Apr 09 '17 at 10:29

Variatus

14,293
2
14
30

Thank you for your answer. My purpose was to change the value in sheet2 H4:H7. The the formula in sheet2 will calculate some equations and output the value in sheet2 E9. The excel function (the one I am writing) will display the value in E9. – JOHN Apr 09 '17 at 11:14
sokinectic = Worksheets("Sheet2").Range("H9").Value This should be Isokinectic = Worksheets("Sheet2").Range("E9").Value – JOHN Apr 09 '17 at 11:40
http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet Don't know whether this can help. – JOHN Apr 09 '17 at 12:02
A UDF won't be able to change values of cells other than the calling cell. A normal function, or a subroutine can, but not a UDF. (There are "hacks" that get around the restriction, but they shouldn't be recommended. And your answer doesn't mention those "hacks".) – YowE3K Apr 11 '17 at 07:55

How to create excel function?

1 Answers1