CODEWITHSUNDEEP
excelvba
Dulini Atapattu
Dulini Atapattu

Reputation: 2735

How to stop VBA function showing formula result in Arguments Dialog in MS Excel

When we add a UDF to MS-Excel using VBA, the function gets evaluated twice when inserting:

1) As soon as all the arguments are given to the function in FUNCTION ARGUMENTS dialog: Hence the result is shown in FUNCTION ARGUMENTS dialog itself as Formula Result

2) After clicking OK in FUNCTION ARGUMENTS dialog: Hence the result is shown in the respective cell.

I also referred to Stop VBA Evaluate from calling target function twice but it made the function does not get evaluated ever.

Can I please know a way of stopping this double evaluation of a UDF function?

Thanks

Upvotes: 0

Views: 383

Answers (1)

gordon613
gordon613

Reputation: 2952

If the problem of the double evaluation is speed considerations (the UDF takes a long time to calculate) then I suggest the following.

  1. adding an extra parameter called doCalculate to the UDF
  2. put FALSE in cell A1 in the worksheet
  3. point the UDF doCalculate parameter to cell A1
  4. add a line of code to exit the function if doCalculate is false

This way the UDF will not calculate from the Function Dialog. However you could activate the UDF by changing A1 to TRUE

Upvotes: 2

Related Questions