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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,485
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLM
30.750P = Probability of a single win
4N = Number of games left
5X = Number of games needed
6CDEFGHIJK
77Mass (Single) Distribution, P(X) (Cum=FALSE)
88NH12: =BINOM.DIST(@X,@N,P,FALSE)
990123456
1010X0100.00%25.00%6.25%1.56%0.39%0.10%0.02%H12: =BINOM.DIST(2,3,P,FALSE)
1111175.00%37.50%14.06%4.69%1.46%0.44%
1212256.25%42.19%21.09%8.79%3.30%H12: =BINOM.DIST(@X,@N,P,FALSE)
1313342.19%42.19%26.37%13.18%
1414431.64%39.55%29.66%
1515523.73%35.60%
1616617.80%
1717Sum100.00%100.00%100.00%100.00%100.00%100.00%100.00%
Binom
Cell Formulas
RangeFormula
C6:K6C6=col()
M8M8=getformula(H12)
E10,K10:K16,J10:J15,I10:I14,H10:H13,G10:G12,F10:F11E10=BINOM.DIST(X,N,P,FALSE)
M12M12=GFX(H12)
B7:B17B7=ROW()
E17:K17E17=SUM(E10:E16)
Named Ranges
NameRefers ToCells
N=Binom!$E$9:$K$9E10
P=Binom!$L$3E10:K10, K11:K16, J11:J15, I11:I14, H11:H13, G11:G12, F11
X=Binom!$D$10:$D$16E10:K10


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

VBA Code:
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

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

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

What am I doing wrong?

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Dislaimer, I have no idea what I am doing here but does this give you any ideas ?
Replace H$12 with you parameter "addr"

VBA Code:
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
 
Upvote 0
Solution
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. (y) So you must have some idea about what you are doing. :unsure:

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.
 
Upvote 0
Thanks to your help, I have it working!!! (y) (y) (y) Thank you! :love:

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

Code:
'=================================================================================
'             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.
 
Upvote 0
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:-

1632961280819.png


1632961158914.png
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top