Rookie in VBA need help

[Manolocs]

Hello, I just got a VBA function from a very nice user of this forum Mr. Peter_SSs. This is my first time using VBA/Macros, so I need some help to figure out what I am doing wrong.

1. I have already the Developer TAB in excel 2013 PRO PLUS
2. Macro security is Enable all Macros
3. Thrust access to the VBA project object model is ticked
4. New workbook > copy past some data needed to column A
5. Developer TAB> VisualBasic> Select Sheet 1> Insert Module> Past the Macro
6. First Dropdown list (General) Second SplitBold automatically
7. Save As a Excel Macro Enabled Workb .xlsm
8. Click on View Microsoft Excel (ALT+F11)
9. Click on Macros>open a Window MacroName: without any saved Macro. Tried both items on the dropdown list (All Projects and Book1) no macro name is available.
10. Tried to run the macro on the VB for Application windows but opens the same windows asking for the Macro name if I write SplitBold the create button is highlighted but comes a msg Name conflicts with existing module, project,or object library.

Any help is more than welcome.
The VBA code is the following:

[COLOR=darkblue]Function[/COLOR] SplitBold(r [COLOR=darkblue]As[/COLOR] Range, [COLOR=darkblue]Optional[/COLOR] bBold [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR] = [COLOR=darkblue]True[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], pos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] Words [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] sBold [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], sUnbold [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  
  [COLOR=darkblue]If[/COLOR] r.Cells.Count = 1 And Len(r.Cells(1).Value) [COLOR=darkblue]Then[/COLOR]
    Words = Split(r.Value)
    pos = 1
    [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](Words)
      [COLOR=darkblue]If[/COLOR] r.Characters(pos, 1).Font.Bold [COLOR=darkblue]Then[/COLOR]
        sBold = sBold & " " & Words(i)
      [COLOR=darkblue]Else[/COLOR]
        sUnbold = sUnbold & " " & Words(i)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      pos = pos + Len(Words(i)) + 1
    [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]If[/COLOR] bBold [COLOR=darkblue]Then[/COLOR]
      SplitBold = Mid(sBold, 2)
    [COLOR=darkblue]Else[/COLOR]
      SplitBold = Mid(sUnbold, 2)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Function
[/COLOR]

 

[jtakw]

Hi,

What you have shown above is not a Macro, it's a Function (UDF), Functions don't show in the macro list, it's meant to be used primarily in formulas, a UDF is used like built in Excel functions such as SUM, IF, COUNT, etc...
If you got this Function code from Peter_SSs in another thread, perhaps you should post back in that thread to inquire how to use this Function.

 

[L. Howard]

With the function in a standard module and these text strings in cells A1 & A2 & A3...

War and Peace

Bold and not bold text

Some text is bold

In B1 =SplitBold(A1) returns "War Peace" non-bold.
In B2 =SplitBold(A2) returns "Bold bold" non-bold.
In B3 =SplitBold(A3) returns "Some" non-bold.

I don't know the original intent of the function.

Howard

 

[Peter_SSs]

As jtakw has indicated, the code is designed to be used by writing a formula in a cell in your worksheet. I did explain in that other thread. Have another look, particularly at step 5 and the screen shot.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formulas as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

.
.
.
.

My Excel jeanie screen shot below will not display partial bold font within a cell so the words that are bold in my sheet are the UPPER case ones below.

Excel Workbook
	A	B	C
1	Data	Bold	Not Bold
2	a BIG book	BIG	a book
3	CAR	CAR
4
5	boat		boat
6	RED apples are nice	RED	apples are nice
7	a big red APPLE		a big red APPLE
Split Bold

 

[Manolocs]

@jtakw thank you for the explanation I did not know it should not show in macro list.

 

[Manolocs]

@L. Howard thanks make sense...

 

[Manolocs]

@Peter_SSs apologies I did not follow all your instructions, I was thinking as a Macro, it is working perfect as I needed. Thanks a lot Peter.

 

[jtakw]

@jtakw thank you for the explanation I did not know it should not show in macro list.

You're welcome.

 

[Peter_SSs]

@Peter_SSs apologies I did not follow all your instructions, I was thinking as a Macro, it is working perfect as I needed. Thanks a lot Peter.

No problem. Glad you got there in the end.