UDF

[QTGlennM]

I found several UDF's on the internet to do what I want them to do. My
question is none of them work and I have tried one that I know to have
worked yesterday but not today on any of my computers, I tried
recalculating the worksheet and everything. Any help would be greatly
appreciated.

thanks
Glenn

 

[Guest]

may your events are disabled try running this code to turn then back on
Sub enableEvents()
Application.enableEvents = True
End Sub

 

[Tim]

Almost impossible to help without seeing any code...

Tim

 

[Niek Otten]

Hi Glenn,

What does "not work" mean? Wrong result? #NAME error? Other error? Excel crashing?

Please be more specific; show the code of the functions, where you paste it, how you call it, value of the arguments, expected
result and what you got instead, etc.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I found several UDF's on the internet to do what I want them to do. My
| question is none of them work and I have tried one that I know to have
| worked yesterday but not today on any of my computers, I tried
| recalculating the worksheet and everything. Any help would be greatly
| appreciated.
|
| thanks
| Glenn
|

 

[QTGlennM]

Hi Glenn,

What does "not work" mean? Wrong result? #NAME error? Other error? Excel crashing?

Please be more specific; show the code of the functions, where you paste it, how you call it, value of the arguments, expected
result and what you got instead, etc.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I found several UDF's on the internet to do what I want them to do. My
| question is none of them work and I have tried one that I know to have
| worked yesterday but not today on any of my computers, I tried
| recalculating the worksheet and everything. Any help would be greatly
| appreciated.
|
| thanks
| Glenn
|

Here is the code. I got it from the internet. It has worked for me
before. And other UDF's as well now none of them work at all.

Function ExtractElement(str, N, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If N > 0 And N - 1 <= UBound(x) Then
ExtractElement = x(N - 1)
Else
ExtractElement = ""
End If
End Function

Here is formula that I use.
=ExtractElement(A1,2,".")

Here is the Error
#NAME?

Any help would be awsome. Thanks for everything.

Glenn

 

[Guest]

The information that it returns #NAME? tells us that Excel cannot see your
UDF from the Workbook that you are using.

There could be one of several issues at play here... but provide us with a
bit more info first.

Where is the UDF code? Is it within the same Workbook that the formula
using it resides? It doesn't sound like it. Perhaps you put it in a
different Workbook or addin? Make sure that when you open up your Workbook
and go into the code IDE (ALT-F11) that your function is entered in a Module
or Sheet from your Workbook and that it is marked Public as the access
modifier.

If these items are true, report back to us, as there might be a different
reference issue at hand.

-putman

 

[Niek Otten]

Hi Glenn,

You didn't even answer half of my questions.
Nevertheless;
You may have pasted the function in a Worksheet or Workbook module.
However, it has to be pasted into a Standard module; In the VBE: Insert>Module

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| > Hi Glenn,
| >
| > What does "not work" mean? Wrong result? #NAME error? Other error? Excel crashing?
| >
| > Please be more specific; show the code of the functions, where you paste it, how you call it, value of the arguments, expected
| > result and what you got instead, etc.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > |I found several UDF's on the internet to do what I want them to do. My
| > | question is none of them work and I have tried one that I know to have
| > | worked yesterday but not today on any of my computers, I tried
| > | recalculating the worksheet and everything. Any help would be greatly
| > | appreciated.
| > |
| > | thanks
| > | Glenn
| > |
|
| Here is the code. I got it from the internet. It has worked for me
| before. And other UDF's as well now none of them work at all.
|
| Function ExtractElement(str, N, sepChar)
| ' Returns the nth element from a string,
| ' using a specified separator character
| Dim x As Variant
| x = Split(str, sepChar)
| If N > 0 And N - 1 <= UBound(x) Then
| ExtractElement = x(N - 1)
| Else
| ExtractElement = ""
| End If
| End Function
|
| Here is formula that I use.
| =ExtractElement(A1,2,".")
|
| Here is the Error
| #NAME?
|
| Any help would be awsome. Thanks for everything.
|
| Glenn
|

 

[Rick Rothstein \(MVP - VB\)]

Here is the code. I got it from the internet. It has worked for me

before. And other UDF's as well now none of them work at all.

Function ExtractElement(str, N, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If N > 0 And N - 1 <= UBound(x) Then
ExtractElement = x(N - 1)
Else
ExtractElement = ""
End If
End Function

Do you know you can do what this function does right in VBA? This one line
will do what the function you posted does...

ExtractedElement = Split(StringIn, DelimiterString)(ItemNumber-1)

(minus the error checking, of course) where StringIn is the original string
you want to split apart, DelimiterString is the delimiter used to separate
your items and ItemNumber is the item in the series that you want, counting
the first item as number one. Here is the one-liner above as a function,
with error checking and using the same argument list as your posted function
(so you won't have to change any existing code)...

Function ExtractElement(str, N, sepChar) As String
On Error Resume Next
ExtractElement = Split(str, sepChar)(N - 1)
End Function


Rick

 

[QTGlennM]

Do you know you can do what this function does right in VBA? This one line
will do what the function you posted does...

ExtractedElement = Split(StringIn, DelimiterString)(ItemNumber-1)

(minus the error checking, of course) where StringIn is the original string
you want to split apart, DelimiterString is the delimiter used to separate
your items and ItemNumber is the item in the series that you want, counting
the first item as number one. Here is the one-liner above as a function,
with error checking and using the same argument list as your posted function
(so you won't have to change any existing code)...

Function ExtractElement(str, N, sepChar) As String
On Error Resume Next
ExtractElement = Split(str, sepChar)(N - 1)
End Function

Rick

Thank you guys for all the advice. I opened a new workbook and pasted
the code into a new module as suggested. It then worked. I apologize
for not being specific I am very very new at this, and you all helped
me a great deal. I thank you for your patience. Thank you so so
much.!!!!

Glenn