Why does my macro keep calling my UDF?

[ransomedbyfire]

I am writing a few macros and UDFs in the same workbook. My UDFs are all in Module 1, and my macros are in their relevant sheets. When I run one of my macros, it jumps into the UDFs once it reads this line:

    Selection.Formula = Cells(13, c).Formula

(The formula in row 13 is "=IF(N13<>"",RANK(N13,$N$8:$N$12),"")". This has nothing to do with my UDFs.)

Why is this, and what can I do to stop it from happening?

Thanks!

 

[Sektor]

UDFs are not allowed to make changes to sheet like coloring, deleting etc. In your case this is what you do. UDF can only return a scalar value or array. Anyway, that's why you write UDF - to get some value. If you want your code to run, make it Sub.

 

[xenou]

It may be that the worksheet is simply triggering a recalculation because of a change in a dependent cell. That's normal behavior for many formulas, UDF or otherwise.

ξ

 

[ransomedbyfire]

Thanks, Xenou. I realized that's what was going on last night. So, I wrote a few lines that set calculation to manual and that manually calculate the sheet when I want it to be done, not when Excel does.

 

[Scott Huish]

Also, it is very rare that you need to Select a cell to work with it in VBA. You can work with ranges directly.

i.e.

Range("A1").Formula=Cells(13, c).Formula

 

[ransomedbyfire]

True. Thanks! I think I let the fact that I am placing the formula over almost an entire column throw me on that one.