Excel VBA Question

I am trying to write a NameExists function subroutine of type Boolean that takes two arguments of type Range and String, respectively.  It will search all of the cells in the given range and check wither any of them have their value property equal to the given string.  For example, if the string is "Bob", it check whether "Bob" is in any of the cells.  A cell containing "John Bob" wouldn't count. The contents have to be "Bob" exactly.  if so, it returns True. Otherwise , it returns False.
MasterOfTheSkyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonkeyOteCommented:
Do you mean a Function or a Sub Routine - sounds more like a Function to me - ie along the lines of:

Function NameExists(rngNames As Range, strName As String) As Boolean
NameExists = Not rngNames.Find(What:=strName, LookAt:=xlWhole) Is Nothing
End Function
0
RichardSchollarCommented:
Hi

You could just use the WorksheetFunction Countif to do this:


Dim blnExists As Boolean

blnExists = Application.WorksheetFunction.Countif(Range("A1:G100"),"Bob")

'blnExists True if present, False otherwise

Note that Countif isn't case-sensitive - if you want case-sensitivity you could use the Find function instead:


Dim rng As Range

Set rng = Range("A1:G100").Find(What:="Bob",LookIn:=xlValues,MatchCase:=True,LookAt:=xlWhole)

'test to see if found:

If Not rng Is Nothing Then
  'value found!
Else
  'Value not found!
End If


Make sense?

Richard
0
MasterOfTheSkyAuthor Commented:
No it does not make sense. I am so sorry,  I don't know VBA to well.  Can you please write it in a sub format.

I tried putting your code in a sub format but it did not work:

Sub NameExist()
Dim blnExists As Boolean

blnExists = Application.WorksheetFunction.CountIf(Range("A1:G100"), "Bob")

'blnExists True if present, False otherwise

End Sub

Please let me know what I did wrong.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DonkeyOteCommented:
If you want to use a separate call to return a value you should use a Function as outlined.

Paste the code I provided into a standard Module, you can then call the Function from your other code, eg:

Sub Example()
Dim strName As String
strName = "Bob"
If NameExists(Range("A1:G100"), strName) Then MsgBox strName & " Has Been Found", vbInformation, "Hooray"
End Sub

Note: per Richard's code if you want case sensitivity (ie Bob <> bob) then you must add the MatchCase argument to the Find call.
0
RichardSchollarCommented:
To see the result using the COuntif you could amend the Sub code you had to:

Sub NameExist()
Dim blnExists As Boolean
Dim strValue As String

strValue = "Bob"

blnExists = Application.WorksheetFunction.CountIf(Range("A1:G100"), strValue)

If blnExists Then
  MsgBox strValue & " Has Been Found", vbInformation, "Hooray"
Else
  MsgBox strValue  & " Has Not Been Found", vbInformation, "YaBoo Sucks!"

End Sub


Richard
0
RichardSchollarCommented:
I missed an End if off the code, that should have been:

Sub NameExist()
Dim blnExists As Boolean
Dim strValue As String

strValue = "Bob"

blnExists = Application.WorksheetFunction.CountIf(Range("A1:G100"), strValue)

If blnExists Then
  MsgBox strValue & " Has Been Found", vbInformation, "Hooray"
Else
  MsgBox strValue  & " Has Not Been Found", vbInformation, "YaBoo Sucks!"
End If

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DonkeyOteCommented:
Richard, would you really advocate a Sub Routine given the request was to "Return a Boolean" ?

I appreciate that a Sub was explicitly requested but I suspect that's more down to familiarity than anything else.  A Sub is illogical here IMO.
0
MasterOfTheSkyAuthor Commented:
I am sorry I am confuse.  Can you please send me and example in and blank excel sheet.  I thing once I see the way it should be setup, I will be able to grasp it.
Tester-a-v1.0.xls
0
DonkeyOteCommented:
Here is one version - using Function based approach.
Q-25848946-MasterOfTheSky.xls
0
MasterOfTheSkyAuthor Commented:
Guys Thank you so much!! I was so confused at first, but now I understand.

DonkeyOte I didn't see your question at first. I meant a Sub-Routine.  
0
DonkeyOteCommented:
I'm glad you have resolved ... as a general principle though if you're "returning" a value you should use a Function rather than a Sub - a Sub won't pass a result back directly whereas a Function will.

Though by no means meant as definitive synopsis it's often useful to distinguish between the two along the lines of:

A Sub is used to perform an action (or actions)

A Function is used to return a value.
0
DonkeyOteCommented:
Chip Pearson's synopsis is, as you would expect, much more eloquent and informative:

http://www.cpearson.com/Excel/differen.htm
0
MasterOfTheSkyAuthor Commented:
Wow!! very informative!  Thanks you!!
0
RichardSchollarCommented:
Luke

Based on the original post, I would have thought a Function to be the appropriate solution :)

Just glad that MasterOfTheSky is happy with the help!

Richard
0
DonkeyOteCommented:
Mr S - you've scuppered my anonymity...
0
MasterOfTheSkyAuthor Commented:
Lol!! I didn't realize you guys know one another.  
0
RichardSchollarCommented:
Yep - all those kids'll be chasing after you trying ot feed you carrots...
0
DonkeyOteCommented:
Yes we do - as a general rule nerds know other nerds... quite a few of us know one another
(though I'm very much a noob round these here parts)
0
MasterOfTheSkyAuthor Commented:
lol!! okay.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.