Code that references the name of a cell

[pwrichcreek]

I have a cell named "devFlag" in my spreadsheet and I want to examine its
contents in VBA code. The cell contains the value "Y", but this code returns
FALSE.

Const cdevFlag As String = "devFlag"
Const cchktot As String = "check_totals"
Function checkdevFlag() As Boolean
Dim rdevFlag As Range
Set rdevFlag = Names.Item(cdevFlag).RefersToRange
If (rdevFlag.Value = "y") Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function

My intent is to use the function in other VBA code, but for testing I've
tried using the function in an IF as:
=IF(checkdevFlag(),"devFlag is y","not") this returns "not"

and as:
=checkdevFlag() this returns FALSE

Can someone see what I am doing wrong?

TIA,

Phil

 

[Robin Hammond]

Phil,

Try this:

Function checkdevFlag() As Boolean
checkdevFlag = (UCase(Range("devFlag").Text) = "Y")
End Function

Robin Hammond
www.enhanceddatasystems.com

 

[Arvi Laanemets]

Hi

Function checkdevFlag() As Boolean
If Lower([cdevFlag])= "y" Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function


PS. You can read the value from any named range in VBA using syntax
[RangeName]. I´m not sure though, what you get when you refer to range which
contains several cells.

 

[Arvi Laanemets]

Sorry! Of course there must be:

.....
If LCase([cdevFlag])= "y" Then



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Hi

Function checkdevFlag() As Boolean
If Lower([cdevFlag])= "y" Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function


PS. You can read the value from any named range in VBA using syntax
[RangeName]. I´m not sure though, what you get when you refer to range
which contains several cells.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

I have a cell named "devFlag" in my spreadsheet and I want to examine its
contents in VBA code. The cell contains the value "Y", but this code
returns
FALSE.

Const cdevFlag As String = "devFlag"
Const cchktot As String = "check_totals"
Function checkdevFlag() As Boolean
Dim rdevFlag As Range
Set rdevFlag = Names.Item(cdevFlag).RefersToRange
If (rdevFlag.Value = "y") Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function

My intent is to use the function in other VBA code, but for testing I've
tried using the function in an IF as:
=IF(checkdevFlag(),"devFlag is y","not") this returns "not"

and as:
=checkdevFlag() this returns FALSE

Can someone see what I am doing wrong?

TIA,

Phil

 

[keepITcool]

problem is case sensitivity, "Y" <> "y"
i also shortened your code to the essentials

Try:

Const cdevFlag As String = "devFlag"

Function checkDevFlag() as Boolean
on error goto errH
checkDevFlag = (UCASE$(Range(cdevFlag)) = "Y")
endH:
exit function
errH:
debug.print "range ";cdevflag;" not found in activeworkbook"
End Function



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pwrichcreek wrote :

 

[pwrichcreek]

Thanks Robin, that did the trick.

Phil

Phil,

Try this:

Function checkdevFlag() As Boolean
checkdevFlag = (UCase(Range("devFlag").Text) = "Y")
End Function

Robin Hammond
www.enhanceddatasystems.com

 

[pwrichcreek]

Thanks, I'll try the ([cdevFlag])= format;; much easier to read and type.

Phil

Hi

Function checkdevFlag() As Boolean
If Lower([cdevFlag])= "y" Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function


PS. You can read the value from any named range in VBA using syntax
[RangeName]. I´m not sure though, what you get when you refer to range which
contains several cells.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

I have a cell named "devFlag" in my spreadsheet and I want to examine its
contents in VBA code. The cell contains the value "Y", but this code
returns
FALSE.

Const cdevFlag As String = "devFlag"
Const cchktot As String = "check_totals"
Function checkdevFlag() As Boolean
Dim rdevFlag As Range
Set rdevFlag = Names.Item(cdevFlag).RefersToRange
If (rdevFlag.Value = "y") Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function

My intent is to use the function in other VBA code, but for testing I've
tried using the function in an IF as:
=IF(checkdevFlag(),"devFlag is y","not") this returns "not"

and as:
=checkdevFlag() this returns FALSE

Can someone see what I am doing wrong?

TIA,

Phil

 

[pwrichcreek]

Thanks keepIT. I got caught up in the notion that, in an EXCEL formula,
=if(devFlag="y") is the same as =if(defFlag="Y"). I should have realized that
VBA would be more precise. It's also very instructive to know that

Dim rdevFlag As Range
Set rdevFlag = Names.Item(cdevFlag).RefersToRange
If (rdevFlag.Value = "y") Then
checkdevFlag = True
Else
checkdevFlag = False
End If

can be reduced to

checkDevFlag = (UCASE$(Range(cdevFlag)) = "Y")

As you no doubt have observed, I'm not a VB programmer, but I have done a
fair amount of perl code. It's refreshing to see that VB does not have to be
as verbose as I may have expected.

Thanks,

Phil

 

[keepITcool]

Wrong!

see help on Evaluate for the specifics of using those brackets..
here's a hint

[cdevflag] returns "devflag" and is NOT equal to [devflag]
[devflag] would evaluate as the value of the named range you're
interested in. btw: [[cdevflag]] doesnt work.

Admittedly it looks clean and good BUT the evaluate method is slower
than using the Range() method

Range(cdevflag) is the syntax of choice.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pwrichcreek wrote :

Thanks, I'll try the ([cdevFlag])= format;; much easier to read and
type.

Phil

Hi

Function checkdevFlag() As Boolean
If Lower([cdevFlag])= "y" Then
checkdevFlag = True
Else
checkdevFlag = False
End If
End Function


PS. You can read the value from any named range in VBA using syntax
[RangeName]. I´m not sure though, what you get when you refer to
range which contains several cells.