CODEWITHSUNDEEP
vbaexcel
KingOfSL
KingOfSL

Reputation: 13

Excel VBA: Naming a range within a user defined function

I am trying to create a user defined function to create a named range and assign a value to the cell. The below code is giving me a #Value error

My code:

Public Function NameARange(CValue As String, NameR As String) as String
Dim ReferAdd As String

ReferAdd = "='" & ActiveSheet.Name & "'!" & ActiveCell.Address

ActiveWorkbook.Names.Add Name:=NameR, RefersTo:=ReferAdd
NameARange = CValue

End Function

Any help to fix this code will be much appreciated. Thanks

Upvotes: 0

Views: 717

Answers (1)

ashleedawg
ashleedawg

Reputation: 21657

Are you sure it's not a #NAME? error that you're getting?

Either way, there are a couple issues with your formula. The short explanation is it doesn't make logical sense.

Let's say you stick that formula in cell A1 of Sheet1...

You're trying to create a named range with a Worksheet Function. Worksheet functions recalculate (re-execute) every time something changes on the worksheet. Excel would try to recreate a new named range by the existing name, over and over and over.

Imagine if you had to sweep the floor anytime sometimes changed in your house... but sweeping the floor changes your house. You'd be stuck in an infinite loop.

You're also want the function to assign a formula to the cell that the function is sitting in. What if you could clone yourself, but the only place that clone could ever stand is exactly where you are standing. Wouldn't work out.

And, finally, you want to finish by returning a value to the same cell that has the function (and the infinite copies of itself)... but not just any value: the value that you called the function with in the first place.

It's like a Catch-22 of a Quagmire of a Paradox.

There is no solution for what you're trying to do except, "don't". Excel won't let you anyhow, which is good because otherwise the universe just might implode.

comic

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

  • Insert, delete, or format cells on the spreadsheet.

  • Change another cell's value.

  • Move, rename, delete, or add sheets to a workbook.

  • Change any of the environment options, such as calculation mode or screen views.

  • Add names to a workbook.

  • Set properties or execute most methods.

The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.

During calculation, Excel examines the precedents of the cell that contains a user-defined function. If not all precedents have been calculated so far during the calculation process, Excel eventually calls the user-defined function and passes a Null or Empty cell to the function. Excel then makes sure that enough calculation passes occur for all precedents to be calculated. During the final calculation pass, the user-defined function is passed the current values of the cells. This can cause the user-defined function to be called more frequently than expected, and with unexpected arguments. Therefore, the user-defined function may return unexpected values.

For correct calculation, all ranges that are used in the calculation should be passed to the function as arguments. If you do not pass the calculation ranges as arguments, instead of referring to the ranges within the VBA code of the function, Excel cannot account for them within the calculation engine. Therefore, Excel may not adequately calculate the workbook to make sure that all precedents are calculated before calculating the user-defined function.

(Source: Microsoft : Description of limitations of custom functions in Excel)

Upvotes: 2

Related Questions