0

I am familiar with this post: How to Return a result from a VBA Function but changing my code does not seem to help.

I want to write a simple function in VBA that allows to lowercase an input sentence. I wrote this:

Private Function Converter(inputText As String) As String
 Converter = LCase(inputText) 
End Function

Sub test()
 Dim new_output As String
 new_output = Converter("Henk")
 MsgBox (new_output)
End Sub

I tried following the advice I found at another stackoverflow post. I made me change this:

Private Function Converter(inputText As String)
 Set outputText = LCase(inputText)
End Function

Sub test()
 Dim new_output As String
 Set new_output = Converter("Henk")
 MsgBox (new_output)
End Sub

However, now I get an error that an object is required. Why does it require an object now? I dont get it...

Henk Straten
  • 1,365
  • 18
  • 39

1 Answers1

0
Set outputText = LCase(inputText)

The Set keyword is reserved for Object data types. Unlike VB.NET, String in VBA is a basic data types.

So you dont Set a variable to a string. Drop the second version of your code altogether. It doesn't make sense. That "advice" was probably in another context.

To fix your first version

1- Assign the returned result to the name of the function Converter 2- It would be beneficial to specify explicitly the return type, as String. Currently it is a Variant that always embeds a String, so better make it explicit:

'                                               vvvvvvvvv
Private Function Converter(inputText As String) As String
  Converter = LCase(inputText) ' <------------ assign return to name of function
End Function
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • Thanks for you advice. Could explain what changes I should make to get this working? – Henk Straten Jun 11 '17 at 13:52
  • @HenkStraten also, although it occasionally works, better use `MsgBox new_output` without parentehsis, that's the correct syntax. – A.S.H Jun 11 '17 at 13:59
  • sorry for bothering. But I just edited my code according to your advice. But still a missing object error. Any thoughts? – Henk Straten Jun 11 '17 at 18:43
  • There's no error now in your first version. As for the second version that comes after *"I tried following the advice I found at another stackoverflow post"* throw it to the thrash bin. – A.S.H Jun 11 '17 at 19:32