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.

Just hints, PLEASE.

Loving the Learning Experience,
Kevin

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

And the answer is.......

Use some String!

Dave :o)OzGrid Business Applications

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

OzGrid Business Applications

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.

Aladin

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

You popped my little bubble

Hi Aladin,

It amazed me. I would never have even thought of the feature, let alone that it was actually available. It seemed like a "poor man's" function writing alternative. I guess I'm just easily amused.

I do admire the wealth of knowledge I've seen demonstrated on this message board, especially by Dave Hawley, Mark W. and of course, you. (In fact, I have also noted a relative newcomer, someone with the initials CW, who seems very adept and knowledgeable.) When I first came to this site, I was directed here because my co-workers looked to me for help. So my head was pretty puffed up until I saw the true giants out there.

Thanks for the note. It is good to hear from you.

Take Care,
Kevin