Using Named Fields

[cstimart]

I have a function similar to this...

Function Test_Num (x as String)
    Text_Num = [YTD_CY_Num]
End Function

In this instance, the x will either be "C" or "P". Is it possible to utilize the x String to determine the "CY" or "PY" of the named field?

 

[Joe4]

What exactly are you trying to do with this function?
Return the value of named range?
For what purpose (where is it going to be used)?

 

[cstimart]

What exactly are you trying to do with this function?
Return the value of named range?
For what purpose (where is it going to be used)?

This isn't the exact function. Depending on the input from the user, I am trying to create or alter which named field might be used.

For example, the choices could possibilities could be...

YTD_CY_Num or YTD_PY_Num

...and since the user would be providing the P or C....

 

[Joe4]

OK, but when you say "named field", do you really mean "named range"?
Or if this some field variable stored somewhere in another procedure?

 

[cstimart]

OK, but when you say "named field", do you really mean "named range"?
Or if this some field variable stored somewhere in another procedure?

Yes, they are specifically named cells on the various worksheets.

 

[Joe4]

Try this:

Function Test_Num(x As String)
 
    Select Case x
        Case "C"
            Test_Num = Range("YTD_CY_Num").Value
        Case "P"
            Test_Num = Range("YTD_PY_Num").Value
        Case Else
            Test_Num = 0
    End Select
    
End Function

 

[cstimart]

That was the option I have implemented, although, I used, for example...

Test_Num = [YTD_PY_Num]



However, my curiosity is whether or not the "P" or "C" from the variable could be directly used in the above example as a 1 line calculation...similar to this

Function Test_Num(x As String)
    Test_Num = Range("YTD_" & x & "Y_Num").Value
End Function

 

[Joe4]

However, my curiosity is whether or not the "P" or "C" from the variable could be directly used in the above example as a 1 line calculation...similar to this

There's one way to find out, try it out! A lot of programming is trial and error. So don't be afraid to try things out.
Does it work?