Macro Button

[davor]

Hi

I am new at macros and VBA editing.
I wanted to split txt form numbers in cells so I putted this code in module:

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby20150306
Dim xLen As Long
Dim xStr As String


xLen = VBA.Len(pWorkRng.Value)


For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
Next
End Function

The code worked but I wanted to have a button that would be on my ribbon. When I want to add it I can't see this macro in the macro list. All others are there.

What am I doing wrong???

 

[steve the fish]

Thats because its a function not a sub. It would have to be rewritten for use with a button.

 

[Yongle]

Functions do not appear in list of macros
Use like any other formula like this

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Text	Value	Formula in B2 copy down
2​	ABC_123	123	=SplitText(A2,TRUE)
3​	XYZ_789	789

Sheet: Sheet3

Or call from a procedure like this

Sub davor()
    Dim Cell As Range, aStr As String, x As Double
    For Each Cell In Range("A2:A3")
        [COLOR=#ff0000]x = SplitText(Cell, True)[/COLOR]
        aStr = aStr & Cell & " : " & x & vbCr
    Next Cell
    MsgBox aStr, , "Numbers Only"
End Sub

With values above, message box returns:
Numbers Only
ABC_123 : 123
XYZ_789 : 789

 

[Yongle]

or call from a button like this to get numbers in active cell

Private Sub CommandButton1_Click()
    MsgBox SplitText(ActiveCell, True)
End Sub

If none of the above gives you what you want, explain exactly how you want the button to work
(- what is the range ? where are returned values placed ? )

 

[davor]

or call from a button like this to get numbers in active cell

Private Sub CommandButton1_Click()
    MsgBox SplitText(ActiveCell, True)
End Sub

If none of the above gives you what you want, explain exactly how you want the button to work
(- what is the range ? where are returned values placed ? )

You solved it. Thank you very much