# Function Writing suggestions

Posted by Kevin James on May 21, 2001 8:38 PM

Hi all. I am continuing my study of VBA at lightning speed (whoa, did you see that snail just race by me?). Okay, so I'm in granny gear.

I just wrote the following function and it works perfectly. (You seasoned veterans will laugh at its simplicity, but it works.):

Function DupFind(apples, oranges) As Boolean
' Created By: Kevin James
If apples = oranges Then DupFind = True Else DupFind = False
End Function

I used this in a single-column, sorted list containing duplicates, giving it the first and second cells (A1,A2) as input and then copying it down.

Now, before I ask my question, please understand that I do NOT WANT you to GIVE me the answer. What I want is for you to give me hints, direct me to VBA constructs and commands.

Here's my question. I was able to take my function and write in cell b2:
=if(DupFind(A2,A1),"duplicate","") so that instead of seeing TRUE or FALSE, I would see an empty cell for false and "duplicate" for true condition. I want to create that as a function but I can't figure out how to give two inputs to a boolean function and return "duplicate" or null.

Loving the Learning Experience,
Kevin

Posted by Dave Hawley on May 21, 2001 8:49 PM

Use some String!

Posted by Kevin James on May 21, 2001 8:59 PM

String? My cat has some. See notes

Hi Dave,

I was hoping you were here. Ya, I know that somehow a string value will come into play, but that is the confusing part. I declared the function as boolean so it won't take a string. I know I can't use a boolean definition, but I can't figure out how to return a string value with based on the results of a boolean operation. I think I'm just confused and need to read more.

by the way, I learned something I've never heard mentioned before. We know about naming ranges, have you ever heard about naming formulas?! While ActiveCell was B2, I created a Named formula ("DupMarker") that had the formula: if(A2=A1,"duplicate",""). Then in cell B2 I typed: =DupMarker. Then I copied that down and the cell references were automatically made relative. I was amazed!

Kevin

Posted by Dave Hawley on May 21, 2001 9:15 PM

Re: String? My cat has some. See notes

You have really opened a can of worms now :o)

Did you want to nest your "DupFind" with an If on the Worsheet to return "Duplicate" or "". Or did you want to do it within Function itself ?

Dave

Posted by Ivan Moala on May 21, 2001 9:23 PM

Lookup IIF

Ivan

Posted by Aladin Akyurek on May 22, 2001 12:26 AM

Named Formulas: Not that extraordinary...

Kevin

You can have named formulas also as an ordinary operation in Excel, not just thru VBA. Not that amazing...

Example:

Activate the option Insert|Name|Define.
Enter as name: CurrentMonth
Enter as ref (that is, as value of Refers To): =TEXT(TODAY(),"MMMM")

Now activate any cell in your workbook and just type:

=CurrentMonth

You'll get May I believe.

Cheers.

Posted by Kevin James on May 22, 2001 12:28 PM

You popped my little bubble