I am having trouble accessing a value in a named range in a UDF

[JenniferMurphy]

The table below does some probability calculations using BINOM.DIST. The function calls in each cell use named ranges X and N for the first 2 parameters (@X & @N). These ranges are:

X is defined as "='Binom'!$D$10:$D$16"
N is defined as "='Binom'!$E$9:$K$9"

I have a UDF (GetFormula) that will display the formula in any cell. I use it here in M8. But I would like to be able to display the values of the first 2 parameters (2,3), rather than "@X,@N", as I show in the literal expression in M10 for the formula in H12.

I started writing GFX (Get Formula eXperimental), another UDF that will call GetFormula, take the result apart, and replace the @X and @N with the values that will be passed to BINOM.DIST from that cell (2 & 3).

GFX does the easy part of the work, as you can see in M12. It calls GetFormula, parses the result, and then puts it back together. All that is missing is the ability to replace "@X" with "2" and "@N" with "3".

Probability (test).xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
3											0.750	P = Probability of a single win
4												N = Number of games left
5												X = Number of games needed
6		C	D	E	F	G	H	I	J	K
7	7	Mass (Single) Distribution, P(X) (Cum=FALSE)
8	8			N								H12: =BINOM.DIST(@X,@N,P,FALSE)
9	9			0	1	2	3	4	5	6
10	10	X	0	100.00%	25.00%	6.25%	1.56%	0.39%	0.10%	0.02%		H12: =BINOM.DIST(2,3,P,FALSE)
11	11		1		75.00%	37.50%	14.06%	4.69%	1.46%	0.44%
12	12		2			56.25%	42.19%	21.09%	8.79%	3.30%		H12: =BINOM.DIST(@X,@N,P,FALSE)
13	13		3				42.19%	42.19%	26.37%	13.18%
14	14		4					31.64%	39.55%	29.66%
15	15		5						23.73%	35.60%
16	16		6							17.80%
17	17		Sum	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%
Binom

Cell Formulas
Range	Formula
C6:K6	C6	=col()
M8	M8	=getformula(H12)
E10,K10:K16,J10:J15,I10:I14,H10:H13,G10:G12,F10:F11	E10	=BINOM.DIST(X,N,P,FALSE)
M12	M12	=GFX(H12)
B7:B17	B7	=ROW()
E17:K17	E17	=SUM(E10:E16)

Named Ranges
Name	Refers To	Cells
N	=Binom!$E$9:$K$9	E10
P	=Binom!$L$3	E10:K10, K11:K16, J11:J15, I11:I14, H11:H13, G11:G12, F11
X	=Binom!$D$10:$D$16	E10:K10

Here's my GFX code. It's quick and dirty code. I'll clean it up after I get it working.

Option Explicit

Function GFX(addr As Range)
Dim formula
Dim pcs() As String
Dim p1, p2, p3, p4, p5

'Get the text of the formula
formula = getformula(addr)

'Split off the function name
pcs = Split(formula, "(", 2, vbTextCompare)
GFX = pcs(0) & "("

'Split the rest at the commas
formula = pcs(1)
pcs = Split(formula, ",", , vbTextCompare)

'Replace P1 & P2 with an expression that will
'return the values of @X & @N at the calling cell
p1 = pcs(0)
p2 = pcs(1)
'These are OK
p3 = pcs(2)
p4 = pcs(3)

'Reassemble the parts
GFX = GFX & p1 & "," & p2 & "," & p3 & "," & p4

End Function

I tried several expressions including

?range("N").Address
$E$9:$K$9

?range("N").Address(2,3).value
$E$9:$K$9

What am I doing wrong?

Thanks

 

[Alex Blakenburg]

Dislaimer, I have no idea what I am doing here but does this give you any ideas ?
Replace H$12 with you parameter "addr"

Sub test()

    Dim narr As Variant
    Dim nCol As Long
    narr = Range("N")
    nCol = Range("H$12").Column - Range("$D$9").Column
    Debug.Print narr(1, nCol)
    
    Dim xarr As Variant
    Dim xRow As Long
    xarr = Range("X")
    xRow = Range("H$12").Row - Range("$D$9").Row
    Debug.Print xarr(xRow, 1)

End Sub

 

[JenniferMurphy]

Dislaimer, I have no idea what I am doing here but does this give you any ideas ?
Replace H$12 with you parameter "addr"

I added your Sub to my code module, changed the Sub statement to Sub test(addr As Range), and called it from my UDF. It worked. So you must have some idea about what you are doing.

Then I moved the code into my UDF and it failed, so I need to do some testing there.

If I understand your code, what you are doing is returning the index of the cells in the X and N ranges, not the actual values (cell contents). This works as long as the values are sequential numbers starting at zero. They may not always be. I need to access the actual contents, because that is what's being passed to Binom.

But you have shown me a way to get the addresses. That gives me something to play with.

Thanks

PS: I was hoping that if I knew a range, like $D$10:$D$16, I could load the values into an array and index that. Now with your addressing approach, I may be able to do just that.

 

[JenniferMurphy]

Thanks to your help, I have it working!!! Thank you!

Here's the code, all cleaned up and pretty:

'=================================================================================
'             GFX = Get Formula eXperimental

' Get the formula at pCellAddr then replace @X & @X with their values
' It works with both limited ($D5:$D10) and unlimited ($D:$D) ranges for X and N

'     To Do:
'=================================================================================
Function GFX(pCellAddr As Range)

Dim temp As String    'Scratch string
Dim formula           'The original formula
Dim pcs() As String   'The pieces when split

Dim nRow As Long      'N values row
Dim xCol As Long      'X values column
Dim cRow As Long      'Cell row number
Dim cCol As Long      'Cell column number
Dim xColLtr As String 'X column letter
Dim cColLtr As String 'Cell column letter
Dim xAddr As String   'X address ($C$R)
Dim nAddr As String   'N address ($C$R)
Dim X As Variant      'X value
Dim N As Variant      'N Value

'Get the row and column numbers
nRow = Range("N").row
xCol = Range("X").Column
cRow = pCellAddr.row
cCol = pCellAddr.Column

'Gte the column letters
temp = Range("X").Address   'Either $C1$R1:$C2:R2 or $C:$C (":")
temp = Mid(temp, 2)         'Delete "$"
pcs = Split(temp, ":", 2)   '.then split on ":"
xColLtr = pcs(0)
temp = pCellAddr.Address
pcs = Split(temp, "$", 3)   '$C$R (no ":")
cColLtr = pcs(1)

'Get the X & N addresses
xAddr = "$" & xColLtr & "$" & cRow
nAddr = "$" & cColLtr & "$" & nRow

'Get the X and N values
X = Range(xAddr).Value
N = Range(nAddr).Value

'Now put it all together
formula = getformula(pCellAddr)             'Text of priginal formula
pcs = Split(formula, "(", 2, vbTextCompare) 'Split off function name
GFX = pcs(0) & "("                          'Add the "("
formula = pcs(1)                            'Split the rest at the commas
pcs = Split(formula, ",", , vbTextCompare)  '.
GFX = GFX & X & "," & N & "," & pcs(2) _
      & "," & pcs(3)                        'Reassemble the parts

End Function

I am sure there are simplifications. I'd be interested in any suggestions.

 

[Alex Blakenburg]

Hello Jennifer,

If I understand your code, what you are doing is returning the index of the cells in the X and N ranges, not the actual values (cell contents).

I thought you might have worked out that this was not the case, but your final code indicates otherwise.

So just to clarify, what I had in my test sub did return the values for N & X that related to the nominated formula address.

eg changing the values to N=8 & X = 9 which intersect at H12, resulted in:-

 

[Alex Blakenburg]

Glad it resolved your issue though and thank you for sharing your final code.

 

[JenniferMurphy]

Hello Jennifer,

I thought you might have worked out that this was not the case, but your final code indicates otherwise.

I admit that I didn't spend a lot of time examining the details of your code. I got the gist of it and went from there. I don't have time right now to examine it or your reply above in more detail, but tell me this: In your opinion, would using your approach be more efficient or reliable than mine? If not, I'll stick with what I have.

Cheers

 

[Alex Blakenburg]

I admit that I didn't spend a lot of time examining the details of your code. I got the gist of it and went from there. I don't have time right now to examine it or your reply above in more detail, but tell me this: In your opinion, would using your approach be more efficient or reliable than mine? If not, I'll stick with what I have.

Cheers

Mine is a bit more direct involving less address string manipulation. On the flip side I have hard coded the top left corner D9 while you didn't need to.

 

[JenniferMurphy]

Mine is a bit more direct involving less address string manipulation.

It's not in a huge loop, so I don't think that much of a factor.

On the flip side I have hard coded the top left corner D9 while you didn't need to.

Oh, right. Now I remember. When I saw that you needed that upper left hand corner as a base reference, I worried that it would make the code less general.

 

[Alex Blakenburg]

And I would agree that more general is better.