text function in vba

[lezawang]

Hi I wrote a code to create a text function that would add "ing" to whatever the user pass as an argument. It did not work. Any idea please. Thank you.

Public Function foo2(text1 As String)
foo2 = text1 & ing
End Function

 

[mikerickson]

You need to treat the ing like and explicit string by adding the quote marks.

Public Function foo2(text1 As String) As String
    foo2 = text1 & "ing"
End Function

 

[Yongle]

How about

Public Function Ing(SomeText As String) As String
    Ing = SomeText & "ing"
End Function

Test
as a formula
=Ing("Work")

as a formula in cell referring to another cell
=Ing(A1)

or using this simple macro

Sub TestFunc()
    MsgBox (Ing(InputBox("Enter a word", "Add ING")))
End Sub

 

[lezawang]

Thanks all for your help. I found the macro below so interesting and fun. However I did not understand the "Add ING"
I know I want to adding ING to the word, but passing it that way is a bit strange to me. I did not understand the flow of the code.
The Ing() function takes 1 argument but here you are passing 2 arguments.

Thank you once again.

How about

Public Function Ing(SomeText As String) As String
    Ing = SomeText & "ing"
End Function

Test
as a formula
=Ing("Work")

as a formula in cell referring to another cell
=Ing(A1)

or using this simple macro

Sub TestFunc()
    MsgBox (Ing(InputBox("Enter a word", "Add ING")))
End Sub

 

[mikerickson]

In that last code, there is only one argument for the Ing function, InputBox("Enter a word", "Add ING").

It is the InputBox function that has two arguments.

 

[Yongle]

I did not understand the flow of the code.

Does this help? Both codes do the same thing.

Without variables

Sub TestFunc()
    MsgBox ([COLOR=#ff0000]Ing([/COLOR][COLOR=#000080]InputBox("Enter a word", "Add ING")[/COLOR][COLOR=#ff0000])[/COLOR])
End Sub

Using variables

Sub TestFunc1()
    Dim aWord As String, addIng As String
    aWord = [COLOR=#000080]InputBox("Enter a word", "Add ING")[/COLOR]
    addIng = [COLOR=#ff0000]Ing([/COLOR]aWord[COLOR=#ff0000])[/COLOR]
    MsgBox addIng
End Sub

 

[lezawang]

Thank you all for all your help. It works so fine. Thank you.
Just one more question. Why when

=foo(1) --> got --> 1ing
=foo(test) --> got--> error message #value
=foo("test") --->got--> testing

why passing 1 is ok but passing, test, is not unless "test" (which I understand) but why 1 is ok

Thanks

 

[Yongle]

why passing 1 is ok but passing, test, is not unless "test" (which I understand) but why 1 is ok

- text always requires " " when inside a formula
- a number on its own usually does not

Try putting these 3 formulas into any cell.
="test"&1
="test"&"1"
="test1"
All return test1

But
=test1
returns error #NAME?

But if this is entered in a cell
test1Excel recognises it as text because it is not inside a formula

<tbody>
</tbody>