UDF function to name a range

Question

I need a little UDF to name a range in Excel but it constantly returns the #VALUE error. Doing the same thing as a sub works like a charm but is not what I'm intending as I want to name big amounts of ranges. First the sub:

Sub setNamedRanges()
    ' input values
    inputRange = "A4"
    newName = "Tank101"

    ' removing spaces from the name
    newName = Replace(newName, " ", "")

    ' write the name
    Range(inputRange).name = newName
End Sub

Now the same as a function (inputRange = "A4" and newName = "Tank101"), it should return "successful" when finished but doesn't work at all:

Function setNamedRange(inputRange, newName)

    ' removing spaces from the name
    newName = Replace(newName, " ", "")

    ' write the name
    Range(inputRange).name = newName

    setNamedRange = "succesful"
End Function

What am I doing wrong? Reading in an array into the sub with the desired values would work for sure but is not giving the full functionality.

Answer 1

Functions can't change workbooks. A range is a workbook/sheet property so can't be changed by a function. Functions can return a result to a sheet but that isn't a property so it's OK. On the flipside, subs can't return anything but they can modify properties.

Answer 2

You can try the below code. It works well:

Sub setNamedRanges()
    Dim newName As String
    newName = "Tank101"
    Dim inputRange As Range
    Set inputRange = Range("A4")
    inputRange.name = newName
End Sub

Please let me know if you have any concern.

Regards

Answer 3

Ok, You can try another ways as below:

Sub Button1_Click()
   Call ChangeValue("Tank101", "Alibaba")
End Sub

Sub ChangeValue(cellAddress, newValue)
    Dim inputRange As Range
    Set inputRange = Range(cellAddress)
    inputRange.Name = newValue
End Sub

Please let me know if you have any concern.

Answer 4

With the help of the Marks answer I found the following solution for my problem, see the solution below. It is a bit long and not straight forward but it works. Calling

giveNameToRange("Tank101")

within the cell gives the name "Tank 101" to this cell. The code is:

Private Declare Function SetTimer Lib "user32" ( _
      ByVal HWnd As Long, _
      ByVal nIDEvent As Long, _
      ByVal uElapse As Long, _
      ByVal lpTimerFunc As Long _
   ) As Long

Private Declare Function KillTimer Lib "user32" ( _
      ByVal HWnd As Long, _
      ByVal nIDEvent As Long _
   ) As Long

Private mCalculatedCells As Collection
Private mWindowsTimerID As Long
Private mApplicationTimerTime As Date

Public Function giveNameToRange(newName) As String

' This is a UDF that returns the sum of two numbers and starts a windows timer
' that starts a second Appliction.OnTime timer that performs activities not
' allowed in a UDF. Do not make this UDF volatile, pass any volatile functions
' to it, or pass any cells containing volatile formulas/functions or
' uncontrolled looping will start.

   newName = Replace(newName, " ", "")
   giveNameToRange = newName

   ' Cache the caller's reference so it can be dealt with in a non-UDF routine
   If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection
   On Error Resume Next
   mCalculatedCells.Add Application.Caller, Application.Caller.Address
   On Error GoTo 0

   ' Setting/resetting the timer should be the last action taken in the UDF
   If mWindowsTimerID <> 0 Then KillTimer 0&, mWindowsTimerID
   mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1)

End Function

Public Sub AfterUDFRoutine1()

' This is the first of two timer routines. This one is called by the Windows
' timer. Since a Windows timer cannot run code if a cell is being edited or a
' dialog is open this routine schedules a second safe timer using
' Application.OnTime which is ignored in a UDF.

   ' Stop the Windows timer
   On Error Resume Next
   KillTimer 0&, mWindowsTimerID
   On Error GoTo 0
   mWindowsTimerID = 0

   ' Cancel any previous OnTime timers
   If mApplicationTimerTime <> 0 Then
      On Error Resume Next
      Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2", , False
      On Error GoTo 0
   End If

   ' Schedule timer
   mApplicationTimerTime = Now
   Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2"

End Sub

Public Sub AfterUDFRoutine2()

' This is the second of two timer routines. Because this timer routine is
' triggered by Application.OnTime it is safe, i.e., Excel will not allow the
' timer to fire unless the environment is safe (no open model dialogs or cell
' being edited).

   Dim Cell As Range

   ' Do tasks not allowed in a UDF...
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Do While mCalculatedCells.Count > 0
      Set Cell = mCalculatedCells(1)
      mCalculatedCells.Remove 1
      Cell.name = Cell.Value
   Loop
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   End Sub