Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't use Autofill with a User Defined Function in VBA
I've created a function that will count the number of cells in a range that
don't contain a formula or are numeric. I named this function =Jonathan() (pretty original eh?) I then have a macro that will find my range (this part I've got figured out) and then add the formula =jonathan(myrange) to the activecell. It then uses the autofill method to fill the cells to the right about 50 something columns. For some reason, all the cells besides the activecell are returning #Value! errors. However, if I do the autofill manually with my mouse after the macro is finished processing, the function works fine. So I know the function works and the ranges are correct. Now, if I replace the formula =jonathan(myrange) in the macro with the Excel built-in function =COUNTA(myrange) then the autofill works fine, granted, I don't get the results I want but the autofill works correctly. Is there some limitation to using autofill in VBA with a User Defined Function? Do I need to add something to my UDF to tell autofill how to treat it? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't use Autofill with a User Defined Function in VBA
A UDF can not change anything on the worksheet, for example it can not format
cells etc. That is the limitation of a user defined function and not of the AutoFill property. "Jonathan Brown" wrote: I've created a function that will count the number of cells in a range that don't contain a formula or are numeric. I named this function =Jonathan() (pretty original eh?) I then have a macro that will find my range (this part I've got figured out) and then add the formula =jonathan(myrange) to the activecell. It then uses the autofill method to fill the cells to the right about 50 something columns. For some reason, all the cells besides the activecell are returning #Value! errors. However, if I do the autofill manually with my mouse after the macro is finished processing, the function works fine. So I know the function works and the ranges are correct. Now, if I replace the formula =jonathan(myrange) in the macro with the Excel built-in function =COUNTA(myrange) then the autofill works fine, granted, I don't get the results I want but the autofill works correctly. Is there some limitation to using autofill in VBA with a User Defined Function? Do I need to add something to my UDF to tell autofill how to treat it? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't use Autofill with a User Defined Function in VBA
Update: I came to find that if I set the Calculation Options to "Manual" at
the start of my code and then set it back to "Automatic" at the end of my code then my UDF worked correctly. Also, rather than using the autofill method, I just added the "=jonathan(myrange)" formula to the entire range of cells that I wanted the formula to be in. The "myrange" ranges changed as needed thanks to relative referencing. "Anant Basant" wrote: A UDF can not change anything on the worksheet, for example it can not format cells etc. That is the limitation of a user defined function and not of the AutoFill property. "Jonathan Brown" wrote: I've created a function that will count the number of cells in a range that don't contain a formula or are numeric. I named this function =Jonathan() (pretty original eh?) I then have a macro that will find my range (this part I've got figured out) and then add the formula =jonathan(myrange) to the activecell. It then uses the autofill method to fill the cells to the right about 50 something columns. For some reason, all the cells besides the activecell are returning #Value! errors. However, if I do the autofill manually with my mouse after the macro is finished processing, the function works fine. So I know the function works and the ranges are correct. Now, if I replace the formula =jonathan(myrange) in the macro with the Excel built-in function =COUNTA(myrange) then the autofill works fine, granted, I don't get the results I want but the autofill works correctly. Is there some limitation to using autofill in VBA with a User Defined Function? Do I need to add something to my UDF to tell autofill how to treat it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |