Using FIND() with UDF string results

[datadrvn]

I have the following UDF, which I'm sure I got from somewhere here, that returns the sheet name.

Public Function SheetInfo(optWanted As Byte) As String
Select Case optWanted
Case 1
SheetInfo = ActiveSheet.Name
Case 2
SheetInfo = ThisWorkbook.Name
Case 3
SheetInfo = ThisWorkbook.FullName
End Select
End Function

The problem is that when I try to use the FIND() function in a cell, say to look for the position of a hyphen in the name, I get a #VALUE error. I can use other string functions, for example LEFT(Sheetinfo(1),1) successfully returns the leftmost character. However, ultimately I'm looking to capture the LEFT [x] number of characters from a hyphen and separately the RIGHT [x] characters from this string.

What am I missing? Thanks...Wayne

 

[datadrvn]

Please disregard. I am being a complete dolt! I am extremely sorry to have bothered anyone with this post. I found a stupidly inserted character in my formula that was messing me up. What a waste of the last 30 minutes.

Sorry.

 

[Jonmo1]

Don't worry about it...
You're not the first, and you won't be the last.

Happy Excelling.

 

[Rick Rothstein]

I have the following UDF, which I'm sure I got from somewhere here, that returns the sheet name.

Public Function SheetInfo(optWanted As Byte) As String
Select Case optWanted
Case 1
SheetInfo = ActiveSheet.Name
Case 2
SheetInfo = ThisWorkbook.Name
Case 3
SheetInfo = ThisWorkbook.FullName
End Select
End Function

I am not sure if you are interested or not, but that UDF you found can be written as a one-liner...

Public Function SheetInfo(optWanted As Byte) As String
  SheetInfo = Choose(optWanted, ActiveSheet.Name, ThisWorkbook.Name, ThisWorkbook.FullName)
End Function

Just a note, though, this code will execute slightly slower than the code you posted because the Choose function will evaluate (expand) each of its arguments before returning the correct one... this will not matter or be measurable if you only use this UDF one (or a few times) but might become noticeable if you use it down, let's say, a long column.

 

[datadrvn]

Hi, Jon and Rick.

Jon...thanks for the encouragement.

Rick...I'm absolutely interested. Yours is much more streamlined. I'm creating a second version now...and will only use the old in those cases that I might need to use extensively in a sheet. Thank you!

 

[Rick Rothstein]

Rick...I'm absolutely interested. Yours is much more streamlined. I'm creating a second version now...

One change you should probably make... I just noticed the data type for the optWanted argument is Byte... you should change it to Long (which is what VB does to it internally when it puts the value into a memory location)... nothing is saved by using a Byte data type (actually, it may cost you, minutely of course, for that conversion time). You should make this change in both the original UDF and the one-liner version I posted.