Hi, I'm trying to run a function to select some data, write it to the calling cell's address and add a named range to encompass that returned data. It's working as expected until it hits the code to create the named range. I have tried adding this range name from the function and tried having the function call a sub to do it but both scenarios fail. I thought I saw a topic a long time ago saying Named Ranges can't be added from a function, but only a Sub but I have not been able to find it again. My results would seem to validate that. Does anyone know for sure if that is an Excel VBA restriction?
Here's the function and the called sub. (BTW, this works if I just run a Sub by itself, but I need it completed in the function process). Any ideas?
Here's the function and the called sub. (BTW, this works if I just run a Sub by itself, but I need it completed in the function process). Any ideas?
VBA Code:
Function BuildDynamicDropdownListRange(MatchObj As String, SearchRange As Range, Position As Integer, NRName As String) As Variant
'Purpose is to read an Array of input data and return an array of items that match in the nth column of the list and create a named range for it.
Dim InArray(), TempArray(), OutArray(), NRParts() As Variant
Dim OutRange As Range
Dim InRows, OutRows As Integer
Dim x, y, OutIdx As Integer
Dim MatchFound As Boolean
Dim NewRef As String
InArray = SearchRange
InRows = UBound(InArray)
ReDim TempArray(1 To InRows)
OutIdx = 0
'Process List
For x = 1 To InRows
'Look for the match
If InArray(x, 1) = MatchObj Then
OutIdx = OutIdx + 1
TempArray(OutIdx) = InArray(x, Position)
End If
Next x
'Write the data
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
OutArray = ResetArray(TempArray)
NRParts = ParseNR(ActiveCell.Address)
NewRef = NRParts(3) & ":" & NRParts(7) & NRParts(8) + OutIdx - 1
BuildDynamicDropdownListRange = Application.Transpose(OutArray())
Call MakeNR(NRName, NRParts(1), NewRef)
'With NRParts(1)
''With Sheets("WattTime Data")
' '.Range("S2:S2") =
' .Range(NewRef) = OutArray
' On Error Resume Next ' an error indicates the NR does not exist
' Names.Add Name:=NRName, MacroType:=1, RefersTo:=Worksheets(NRParts(1)).Range(NewRef)
'End With
'set the named range
'OutIdx = OutIdx - 1
'ReDim Preserve OutArray(OutIdx)
On Error GoTo 0
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Erase InArray, TempArray, OutArray
End Function
Sub MakeNR(ByVal NRName As String, ByVal NRSheet As String, ByVal NRAddress As String)
Debug.Print "Sub MakeNR Called at ", Time()
On Error GoTo Handle:
'On Error Resume Next
Names.Add Name:=NRName, MacroType:=1, RefersTo:=Worksheets(NRSheet).Range(NRAddress)
'End With
Debug.Print "Sub MakeNR Exited at ", Time()
Exit Sub
Handle:
'Debug.Print Err, Err.Description, Err.Source, Err.HelpContext, Err.HelpFile
Debug.Print "Sub MakeNR Exited Handle at ", Time()
End Sub
