Referencing a UDF in Range.Formula Property - NAME#? Error???

[ilsley_excel]

Hi,

I'm trying to reference a UDF I've built in the .Formula property (within VBA), but it keeps giving me the NAME#? error! This is how I'm trying to reference it:

Worksheets(1).Range("AG2").Formula = "=MyUDF(A2,K2,1)"

Edit: Corrected typo!

If I use the UDF directly from the cell, it works fine, but I need to be able to put the formula directly into the cell via VBA.


Any ideas? Thanks!

[mgs73]

Hi - it has nothing to do with your UDF. Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and then it should work.

[TMS]

Range is not a property of the workbook, it is a property of the worksheet.

Please Login or Register  to view this content.

Regards, TMS

[Richard Buttrey]

Hi,

Have you considered holding the UDF formula in a named cell somewhere and in VBA copy that cell to your AG2 cell?

[TMS]

@Richard: we don't know what the UDF does, do we? I just made something up to demonstrate the solution.

[Richard Buttrey]

@Trevor

No we don't. Like you I created a test UDF =MyUDF(B1,C1) in A1

Then in VBA say

Please Login or Register  to view this content.

which would then evaluate numbers in B10 & C10.

...or am I missing some subtlety here?

Incidentally on the other little local 'difficulty' with LJLA now up to 10 months and counting

[TMS]

Incidentally on the other little local 'difficulty' with LJLA now up to 10 months and counting

I never, ever, heard anything back from VCS or POPLA. Program (repeated) on the BBC the other day, Rip Off Britain, featured this issue but it was a bit of a waste of time and seemed, if anything, to side with LJLS/VCS.

[ilsley_excel]

Hi - it has nothing to do with your UDF. Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and then it should work.

Sorry, that was a typo... I was rushing when I posted this earlier! It does actually say Worksheets(1).Range in my code but it still gives me the same #NAME? Error.

[TMS]

Think you need to post a sample workbook that demonstrates the problem.

[ilsley_excel]

Think you need to post a sample workbook that demonstrates the problem.

Sorry for the delayed response. I managed to sort it out earlier, but thanks to everyone who posted!

[TMS]

You're welcome. Thanks for the rep.


Care to share what the problem was and what you did to sort it out?


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.