0

In excel, I use a long formula (with only one argument) very often.

Is there any way to make it a "custom" function(UDF) without using VB (like the way a user can create custom styles and template in Word)?

If no, is there any way (such as a website) to convert an excel formula to VB code?

0

1 Answer 1

1

Create UDF like method, or I can say an UDF without VBA in Excel, and convert an excel formula to VBA code are two different issues.

Let me take one by one.

1. UDF without VBA:

Yes is possible, and you can do this by using NAMED RANGE method.

How it works:

  • Write the formula in cell you want to use.
  • Select the cell.
  • Now to get the Name Manager either use Ctrl+F3 or from FORMULA tab, hit the Name Manager.
  • Click on New then assign an appropriate Name to the cell, and finish with Ok.

Now you can use that name as an UDF, Excel will use that formula.

Remember this works with simple and straight forward Formula only.

2. Convert Excel Formula to VBA Code:

This method should consider as how to use Excel formula with/as/through VBA code.

  • There are many methods, few I would like to show here.

    Range("G2").Formula = "=IFERROR(VLOOKUP(A1,$A$5:$E$25,2,FALSE),"")
    
  • This assigns a formula to the cell G2.


   X = Application.WorksheetFunction.IFERROR(VLOOKUP(A1,$A$5:$E$25,2,FALSE),"")
  • This assigns formula to the variable X.

N.B.

Formula used above are editable.

You may refer Microsoft website for further assistance.

6
  • I didn't understand the formula in part 2. Can you please elaborate?
    – Shub
    Dec 8, 2020 at 8:45
  • 1
    I voted this up even though the 2nd part is not the best option. It basically pastes a function into that cell. Instead you can create a new VBA function and use =Name of function to get it to do what you want. The downside is that VBA does not use excel formulas as code, so its much harder to construct/maintain. For what you want the first option is definitely the way to go anyway.
    – LPChip
    Dec 8, 2020 at 9:56
  • Although the method described in part 1 is very close to what I needed, there's no way to pass any argument in the function.
    – Shub
    Dec 8, 2020 at 11:49
  • 1
    Well yes you can pass an argument to the named range via an input cell (relative to the position of the cell the named range is being used in). I.e. if you define the named cell say "rightplus1" with your cursor in cell A1, and enter the formula as =B1+1 (not $B$1) then you can use =rightplus1 in any cell and it will return ="cell to the right" + 1 Dec 8, 2020 at 15:55
  • @Shub,, the formula in part 2 is just an example where first code inserts/assigns formula to cell G2 (it should any cell/Range of cells even), and the second code assigns formula to a variable,, later on you may use this variable's value to pass into any cell or can use for any further calculations. Now check I've edited the post and mentioned all about part two. Dec 9, 2020 at 8:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .