Simple vba FUNCTION that uses NAMED RANGES

LOOKING AT THE ATTACHED WORKBOOK, please note that I created a 3x3 array on the only worksheet and defined named ranges as can be seen in the NameManager.

Then go to the code.

You will see the beginnings of a simple UDF and a subroutine that i intend to use to test the function.

The Subroutine must fetch named ranges from the workbook and pass it on to my UDF as the first parameter.

Clearly I do not understand how to successfully pass the named array to the subroutine. After I understand that, I can try and make the subroutine attempt to use that named array as a parameter for my UDF, the beginnings you can see.

Please see the attached workbook to see the code

UDF

Code	
Function ExtractDoorDimensions(InputDoorDataSet As Range, DoorDimension_Ptr As Integer)	
' DoorDataSet is a variable of type range for a named range defined in the workbook. It is the first parameter.	
'    ie: input will be Door01 without quotes (Do NOT use quotes)	
'    one can go to the spreadsheet and click on formulas and then on name manager to see what the named array Door01 consists of	
' DoorDimension_Ptr is the second parameter (of type integer) and is a named range.	
' It will be DoorWidth_Pointer as defined in the workbook name manager	
	
Dim aDoorDataSet As Range	
Dim a	
	
Set aDoorDataSet = InputDoorDataSet	
' look at local variables to see if	
' the following code is arbitrary. merely there to at least see the debug step is working	
a = Application.WorksheetFunction.Index( _	
    aDoorDataSet, DoorDimension_Ptr)	
ExtractDoorDimensions = a	
    	
End Function

PHP Code: 

Door01    =ExtractDoorDimensions(G5, 1)
80.00    =ExtractDoorDimensions(H5:I5, 1)
24.00    =ExtractDoorDimensions(H5:I5, 2)
    
Door01    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A1))
80.00    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A2))
24.00    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A3))
    
80.00    =ExtractDoorDimensions(Door01, 1)
24.00    =ExtractDoorDimensions(Door01, 2)
    
80.00    =ExtractDoorDimensions(Door01, DoorHeight_Pointer)
24.00    =ExtractDoorDimensions(Door01, DoorWidth_Pointer) 


Hi whburling,

See the attached with a better approach that will answer your question (maybe).
VBA Ed for whburling.xlsm

No, I said:

The Pointers must be 1 and 2 respectively

In your example, if you want to use the Named Ranges for Door01, Door02, Door03, Door04, etc., which are two columns wide, the pointers can only have a value of 1 or 2. You had them set as 2 or 3. That COULD be ok with some of my earlier examples which are not using Named Ranges and, therefore, not constrained by the size of the range.

Hence my pointers are 1 and 2.

No, in the example you uploaded, the pointers were 2 and 3.

PHP Code: 

DoorHeight_Pointer    DoorWidth_Pointer
          2                    3 


to recap, i am not passing the entire 3x3 array.
I am passing only one 1x2 array named doorxx

l know that, hence you can only index the first or second element in that array.

Did you look at the file I uploaded?

In Windows File Manager, navigate to the folder where you download files. Right click on the .xlsm file and select Properties. Near the bottom, you will see a check box that says Unblock. Click in that and ok out. You should now be able to open the file and enable macros.

No worries.

Hi whburling,
See if this attached makes more sense to you.
I think wanting to program with Arrays and a Userform are more than you really need.
VBA Ed Electrical Calculations from Marv.xlsm
You need to open the attached and then save it to your hard drive and re-open it from there for the VBA to work. That way downloaded Excel with VBA can't unknowingly screw thing up.

Thank you....wish i could give you another reputation.

Me too, but not to worry, it's the thought that counts

Whilst I understand what you are trying to do, I really do NOT understand why you want to do it. There are countless ways of using formulae, with or without Named Ranges, to search for and return the data you want. You really do NOT need VBA for this.

Here is the revised code.

Function ExtractDoorDimensions(InputDoorDataSet As Range, DoorDimension_Ptr As Integer) As Single
 Dim a As Single
 
 a = Application.WorksheetFunction.Index(InputDoorDataSet, DoorDimension_Ptr)
 ExtractDoorDimensions = a
End Function

Sub Test_EDD()
    Dim InputArray As Range
    Dim Ptr As Integer
    Dim a As Single

    'Set InputArray = ThisWorkbook.Names("Door24").RefersToRange
    Set InputArray = Range("Door24")            ' range to be indexed
    Ptr = Range("DoorWidth_Pointer").Value      ' index value
    a = ExtractDoorDimensions(InputArray, Ptr)  ' extract function
    Debug.Print a
End Sub

Sub Test_Short_EDD()
    Dim a As Single

    a = ExtractDoorDimensions(Range("Door24"), Range("DoorWidth_Pointer").Value)
    Debug.Print a
End Sub