Forum Discussion

Lost1nExcel's avatar
Lost1nExcel
Copper Contributor
Mar 29, 2023

Excel Custom Function

I am trying to write a custom function for rounding a set of data. So that I can reuse it for each form I create and not have to rewrite the whole function. I have tried to use Macros and VBA but fr...
excel
Formulas and Functions
Macros and VBA
peiyezhu's avatar
peiyezhu
Bronze Contributor
Mar 29, 2023
not have to rewrite the whole function.

just short length of function?
how about name the formula as f?

then =f(...)

https://support.microsoft.com/zh-cn/office/%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E5%AE%9A%E4%B9%89%E5%92%8C%E4%BD%BF%E7%94%A8%E5%90%8D%E7%A7%B0-4d0f13ac-53b7-422e-afd2-abd7ff379c64#:~:text=%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E4%BD%BF%E7%94%A8%E5%90%8D%E7%A7%B0%201%20%E9%80%89%E6%8B%A9%E4%B8%80%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%EF%BC%8C%E7%84%B6%E5%90%8E%E8%BE%93%E5%85%A5%E5%85%AC%E5%BC%8F%E3%80%82%202%20%E5%B0%86%E5%85%89%E6%A0%87%E7%BD%AE%E4%BA%8E%E5%85%AC%E5%BC%8F%E4%B8%AD%E8%A6%81%E5%BA%94%E7%94%A8%E6%AD%A4%E5%90%8D%E7%A7%B0%E7%9A%84%E4%BD%8D%E7%BD%AE%E3%80%82%203%20%E9%94%AE%E5%85%A5%E5%90%8D%E7%A7%B0%E7%9A%84%E9%A6%96%E5%AD%97%E6%AF%8D%EF%BC%8C%E7%84%B6%E5%90%8E%E4%BB%8E%E5%87%BA%E7%8E%B0%E7%9A%84%E5%88%97%E8%A1%A8%E4%B8%AD%E9%80%89%E6%8B%A9%E5%90%8D%E7%A7%B0%E3%80%82,%E6%88%96%E9%80%89%E6%8B%A9%E2%80%9C%20%E5%85%AC%E5%BC%8F%20%E2%80%9D%3E%E2%80%9C%20%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E4%BD%BF%E7%94%A8%20%E2%80%9D%E5%B9%B6%E9%80%89%E6%8B%A9%E8%A6%81%E4%BD%BF%E7%94%A8%E7%9A%84%E5%90%8D%E7%A7%B0%E3%80%82%204%20%E6%8C%89Enter%E9%94%AE%E3%80%82
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Mar 29, 2023

    To name an Excel formula, you can follow these steps:


    Select the formula cell, for example, A1.

    In the formula bar, enter the desired name, for example, "my_formula", and press Enter. The formula in cell A1 will still exist.

    Select cell A1 and click the "Name Manager" button (under the "Formulas" tab).

    In the "Name Manager" dialog box, click the "New" button.

    In the "New Name" dialog box, enter the name "my_formula" (or any other name you want) and in the "Refers to" field, enter the formula "=let(x,1,3+x)" (or any other formula you want).

    Click the "OK" button and then close the "Name Manager" dialog box.

    Now, you can type "my_formula" in any cell, and Excel will automatically replace it with the formula "let(x,1,3+x)".
    You can also name formulas with parameters by following similar steps. Here is a link to Microsoft's documentation on using names in formulas: https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64.




    • Lost1nExcel's avatar
      Lost1nExcel
      Copper Contributor
      Mar 29, 2023

      peiyezhu

      thank you for your help, I am still confused on forming the whole thing.

      What can I use in the formula to set reference to the cell selected by the user.

      for example, if I want to do simple addition:

      =sum(F3+F4)

      F3 and F4 were each clicked on the user after starting the formula.

      or in your example “x”

      For my formula,

      I want the user to insert the formula name and select the desired cell they wish to use. What do I write to reference to the current selected cell by the user in:

      IF(ISODD(Cell),CEILING(CELL,1),FLOOR(X,1))

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Mar 29, 2023
        =sum(F3+F4)
        name as =sum(x+y)
        then =my_formula(x,y)

        as to
        IF(ISODD(Cell),CEILING(CELL,1),FLOOR(X,1))

        I am not familiar with formlas,so I do not know what is the exact mean about your function.

        Hope other experts come with other suggestions.

Resources