Using VBA to populate cells with formulas containing user-defined functions

Hi,

As per title, I'm trying to populate some formulas from VBA but having problems. This is the UDF and populating sub:

Please Login or Register  to view this content.

When I run it I just get the hourglass until I hit escape. It puts the formula into the first cell correctly but it has a '#VALUE!' error and does not continue with the other cells. If I then hit 'calculate' the formula works fine.

edit - I've now found that if I leave the calculation as automatic it will populate all the cells but still hangs afterwards.

Been messing around for a while with this so any suggestions would be gratefully received.

Thanks,

Chinchin

(deleted because it was wrong)

Do you need to use INDIRECT in the formula?

Please Login or Register  to view this content.

In this case the formula will not change when dragged it is inserted as an absolute reference by the code
e.g.

Please Login or Register  to view this content.

By using the $ "anchors" you can decide whether the formula changes or not as it is dragged.

INDIRECT() is a volatile function and will recalculate every time your workbook calculates, this can be very memory intense, so it is best avoided if possible.

Best you protect the sheet to avoid users dragging formula at will.

Try the code I posted in Post #5

It will recalculate the volatile functions every time the workbook changes and recalculates anything, regardless if the formula is on the active worksheet or not.
See this link
... Volatile and Non-Volatile Functions

By locking/unlocking cells then protecting the sheet, you can decide what cells the user has access to. You can allow the user to edit/enter data in a cell but not be able to drag it.
See this link
Lock or unlock specific areas of a protected worksheet

Avoid using the volatile formulae where-ever possible, certainly never use them to "protect" formula.

Please find attached.

The 'formulas' table needs to always show the values form the corresponding position in the data entry table regardless of what the user does (for example, try dragging the '1' to another cell - the formulas using INDIRECT show what needs to happen).

Preventing the user from dragging would be ok too, but the way the INDIRECT formulas work is preferable since dragging can speed up the data entry and users are used to it being available.

Thanks,

Chinchin

Looks like Named Ranges and/or Sheet Protection might be your best options.

1/. Create a Named Range for the Data Entry Table
Name:= DataEntryForm
Refers To :=

Please Login or Register  to view this content.

2/. In G3

Please Login or Register  to view this content.

Drag Across to I3 Then Down to Row 7

3/. Protect the sheet but allow manual entry to the Data Table.
Select B3:D7
Format > Format Cells ...
In the Protection Tab Uncheck Locked > Press Okay

Format > Protection:=Protect Sheet
You can enter a password and select the desired options, but for this exercise just Press Okay

Try what you need to do with the Data Table.

Now unprotect the sheet
Format > Protection:=Unprotect Sheet

Select B3:D7 and drag the whole table somewhere else on the sheet.
The new formula should still display correctly, but the Indirect method will fail.

This is just to illustrate what is possible to control, there are many more permutations that you can use.

Way to go buddy ...
Happy to have helped.