text function in vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,080
You need to treat the ing like and explicit string by adding the quote marks.
Code:
Public Function foo2(text1 As String) As String
    foo2 = text1 & "ing"
End Function
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
How about

Code:
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
Code:
Sub TestFunc()
    MsgBox (Ing(InputBox("Enter a word", "Add ING")))
End Sub
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
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
Code:
Sub TestFunc()
    MsgBox (Ing(InputBox("Enter a word", "Add ING")))
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,080

ADVERTISEMENT

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.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I did not understand the flow of the code.

Does this help? Both codes do the same thing.

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

Using variables
Code:
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:

Forum statistics

Threads
1,148,291
Messages
5,745,892
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top