VBA excel - Calling a function from excel

I wrote a VBA program to remove vowels. I am not able to call the function from excel. i get a #NAME error. Code below

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 REMOVEVOWELS = ""

 For i = 1 To Len(Txt)

     If Not UCase(Mid(Txt, i, 1)) Like "(AEIOU)" Then

         REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)

     End If

 Next i

 End Function

Code tags inserted by Tim Stack

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

It does work as in it doesn't give a #Name error. It doesn't take out the vowels either though.

– Darren Bartrup-Cook
Nov 28 '18 at 10:44

add a comment |

I wrote a VBA program to remove vowels. I am not able to call the function from excel. i get a #NAME error. Code below

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 REMOVEVOWELS = ""

 For i = 1 To Len(Txt)

     If Not UCase(Mid(Txt, i, 1)) Like "(AEIOU)" Then

         REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)

     End If

 Next i

 End Function

Code tags inserted by Tim Stack

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

It does work as in it doesn't give a #Name error. It doesn't take out the vowels either though.

– Darren Bartrup-Cook
Nov 28 '18 at 10:44

add a comment |

I wrote a VBA program to remove vowels. I am not able to call the function from excel. i get a #NAME error. Code below

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 REMOVEVOWELS = ""

 For i = 1 To Len(Txt)

     If Not UCase(Mid(Txt, i, 1)) Like "(AEIOU)" Then

         REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)

     End If

 Next i

 End Function

Code tags inserted by Tim Stack

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

I wrote a VBA program to remove vowels. I am not able to call the function from excel. i get a #NAME error. Code below

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 REMOVEVOWELS = ""

 For i = 1 To Len(Txt)

     If Not UCase(Mid(Txt, i, 1)) Like "(AEIOU)" Then

         REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)

     End If

 Next i

 End Function

Code tags inserted by Tim Stack

excel vba

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

edited Nov 28 '18 at 12:04

Cindy Meister

15.8k102437

asked Nov 28 '18 at 10:28

user9020604

asked Nov 28 '18 at 10:28

user9020604

asked Nov 28 '18 at 10:28

user9020604

It does work as in it doesn't give a #Name error. It doesn't take out the vowels either though.

– Darren Bartrup-Cook
Nov 28 '18 at 10:44

add a comment |

It does work as in it doesn't give a #Name error. It doesn't take out the vowels either though.

– Darren Bartrup-Cook
Nov 28 '18 at 10:44

It does work as in it doesn't give a #Name error. It doesn't take out the vowels either though.

– Darren Bartrup-Cook
Nov 28 '18 at 10:44

add a comment |

2 Answers
2

active

oldest

votes

Rewritten, tested, and works! Insert this at the top of a Module

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 For i = 1 To Len(Txt)

    If Mid(Txt, i, 1) Like "[AEIOU]" Then

        Txt = Replace(Txt, Mid(Txt, i, 1), "")

    End If

 Next i

REMOVEVOWELS = Txt

 End Function

EDIT

A more elegant solution.

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

Vowels = Array("A", "E", "I", "O", "U")



For Each a In Vowels

    Txt = Replace(Txt, a, "")

Next a

REMOVEVOWELS = Txt

 End Function

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

1

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

1

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

|
show 3 more comments

Regardign the #NAME error (not about the logic) - most likely you've added function at Application of Sheet level in VBA. It must be added in separate module instead (Right click on VBAProject - Insert - Module)

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53517274%2fvba-excel-calling-a-function-from-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Rewritten, tested, and works! Insert this at the top of a Module

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 For i = 1 To Len(Txt)

    If Mid(Txt, i, 1) Like "[AEIOU]" Then

        Txt = Replace(Txt, Mid(Txt, i, 1), "")

    End If

 Next i

REMOVEVOWELS = Txt

 End Function

EDIT

A more elegant solution.

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

Vowels = Array("A", "E", "I", "O", "U")



For Each a In Vowels

    Txt = Replace(Txt, a, "")

Next a

REMOVEVOWELS = Txt

 End Function

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

1

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

1

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

|
show 3 more comments

Rewritten, tested, and works! Insert this at the top of a Module

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 For i = 1 To Len(Txt)

    If Mid(Txt, i, 1) Like "[AEIOU]" Then

        Txt = Replace(Txt, Mid(Txt, i, 1), "")

    End If

 Next i

REMOVEVOWELS = Txt

 End Function

EDIT

A more elegant solution.

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

Vowels = Array("A", "E", "I", "O", "U")



For Each a In Vowels

    Txt = Replace(Txt, a, "")

Next a

REMOVEVOWELS = Txt

 End Function

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

1

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

1

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

|
show 3 more comments

Rewritten, tested, and works! Insert this at the top of a Module

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 For i = 1 To Len(Txt)

    If Mid(Txt, i, 1) Like "[AEIOU]" Then

        Txt = Replace(Txt, Mid(Txt, i, 1), "")

    End If

 Next i

REMOVEVOWELS = Txt

 End Function

EDIT

A more elegant solution.

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

Vowels = Array("A", "E", "I", "O", "U")



For Each a In Vowels

    Txt = Replace(Txt, a, "")

Next a

REMOVEVOWELS = Txt

 End Function

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

Rewritten, tested, and works! Insert this at the top of a Module

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

 Dim i As Long

 For i = 1 To Len(Txt)

    If Mid(Txt, i, 1) Like "[AEIOU]" Then

        Txt = Replace(Txt, Mid(Txt, i, 1), "")

    End If

 Next i

REMOVEVOWELS = Txt

 End Function

EDIT

A more elegant solution.

Function REMOVEVOWELS(Txt) As String

'Removes all vowels from the Txt argument

Vowels = Array("A", "E", "I", "O", "U")



For Each a In Vowels

    Txt = Replace(Txt, a, "")

Next a

REMOVEVOWELS = Txt

 End Function

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

edited Nov 28 '18 at 11:01

answered Nov 28 '18 at 10:48

Tim Stack

26312

answered Nov 28 '18 at 10:48

Tim Stack

26312

answered Nov 28 '18 at 10:48

Tim Stack

26312

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

1

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

1

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

|
show 3 more comments

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

1

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

1

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

Now that I think of it... Perhaps you are better off just adding a Replace function for each of the 5 vowels instead of looping through all characters in the string. What my code does now is replacing all of one vowel in the string anyway once it finds said vowel. Running the Replace function five times should not put a lot of strain on Excel

– Tim Stack
Nov 28 '18 at 10:55

I hadn't spotted the square bracket mistake. So the original code posted by OP does work if he uses instead of () and puts the code in the correct place. But, as you say - REPLACE would be simpler.

– Darren Bartrup-Cook
Nov 28 '18 at 10:56

That's right, I have added a more elegant solution that does not require the function to loop through all characters

– Tim Stack
Nov 28 '18 at 11:16

Dr Mr Bartrup-Cook and Mr Stack. I copied your code and also inserted Square brackets to specify ranges. I have also placed this code in a new VBA module, I am still getting a #NAME error.

– user9020604
Nov 28 '18 at 11:33

Are you pasting the code in the same workbook as the one you're using the formula in? What kind of strings are you adding to the formula?

– Tim Stack
Nov 28 '18 at 11:42

|
show 3 more comments

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

add a comment |

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

add a comment |

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

edited Nov 30 '18 at 6:21

answered Nov 28 '18 at 10:43

sarh

4,72941720

answered Nov 28 '18 at 10:43

sarh

4,72941720

answered Nov 28 '18 at 10:43

sarh

4,72941720

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl