Why does IsError return #value

[asaxsma]

For some reason my use defined function udf_tf that uses IsError returns #value. See attachment.

[Richard Buttrey]

Maybe instead

Please Login or Register  to view this content.

[Rick Rothstein]

@Richard Buttrey,

If your UDF does what the OP wants, then I believe this one should as well...

Please Login or Register  to view this content.

[Richard Buttrey]

Thanks Rick...even better

[asaxsma]

Rick/Rich - Thanks guys! Hmmm. Unfortunately, neither worked. Not sure why not. Both are always TRUE. See attached.

[asaxsma]

Ope! Arguments to InStr were backwards. Thanks for the solution guys!

Side question, I've got some coding experience, but I'm new to VBA and new to VBA in the Excel platform. Question 1: All I know about are normal in-cell Excel functions. What is the story with VBA only Excel functions like "InStr()"? Question 2: You know of any general tutorials or references that would help me to learn?

Thanks again guys for the quick reply and awesome solution. You guys are the best!!

[MrShorty]

I find these two references useful:

VBA language reference with information about VBA statements, functions, keywords, etc. https://docs.microsoft.com/en-us/off...uage-reference

Since most uses of VBA (in this forum) are interacting with Excel, this documents the object model for Excel: https://docs.microsoft.com/en-us/off...l/object-model

[Richard Buttrey]

The syntax for the various VBA instructions can be found in the VBE and hitting F2 to bring up the Object Browser (or View Obect Browser)

Click the VBA option in the top dropdown and you'll see all the instuctions in the 'Members' pane. You can further filter these with the Classes pane, so for instance selecting the Strings class you'll see all the instructions that you can use for strings of data.

As for learning, well of course everyone is different. I used two books

Excel 2000 VBA Programmers Reference Guide but of course this has been updated in the last 20 years and later versions are available. I don't believe that VBA (unlike Excel itself) has changed much if at all over the period. ISBN 1-861002-54-8

The other was 'Writing Excel Macros with VBA' by Steven Roman ISBN 1-56592-587-4

If you google particular requirements including the words Excel & VBA you'll find many sites that contain stuff that you'll find useful, many also have videos.

The other way of course is to watch this forum and note the sorts of questions people ask and the development of the answer. Try following the suggestions yourself and play around with modifying code to see what happens.

Important things to remember.
In Excel avoid merged cells. Trying to handle merged cells with VBA can be a nusiance and you'll get unexpected results.
In Excel always use range names, preferably dynamic names that change automatically to cover ranges that expand or contract over time, and use the names in VBA rather than A1 type cell references.

Hope this helps a little

[asaxsma]

Thanks again guys, I will have a look at the references. Anything I should do to close out this thread? I didn't see any likes, votes, or solution markers.

[Richard Buttrey]

Here's the text of the standard requirement.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.