extracting 1st letter of each word in a string

[jajol]

sort of name initial..

thanks & regards

 

[hedonist]

Andrew, I doubt it. The PC we're testing it on is standing next to mine and VBA is being used daily on it. I also had the same problem when I took the file home, or whenever I copy the file onto a new PC.

Will it maybe work if I copy the function to the personal sheet of all the computers?

 

[Andrew Poulsom]

In what way does it "bomb out"?

 

[hedonist]

The workbook is on automatic calculation, so whenever a cell value is changed the "initials" function tries to run. The error that is returned is:

"Compile Error: Can't find project or library"

and it keeps on saying it until you close Excel. I just tried some other computers and it is fine on my PC (Win2k and OfiiceXP, where the code was originally written) as well as another one running Win2k and Office2k.

It seems to be only working in Win2k and not WinXP or 98, regardless of the Office version.

The strange 77th dimension of computers.....

 

[BJungheim]

Go to Tools|References and check the References against the computer it DOES work on. Something tells me you're missing something...

 

[Andrew Poulsom]

The workbook is on automatic calculation, so whenever a cell value is changed the "initials" function tries to run. The error that is returned is:

"Compile Error: Can't find project or library"

and it keeps on saying it until you close Excel. I just tried some other computers and it is fine on my PC (Win2k and OfiiceXP, where the code was originally written) as well as another one running Win2k and Office2k.

It seems to be only working in Win2k and not WinXP or 98, regardless of the Office version.

The strange 77th dimension of computers.....

That's what I expected and why I said "sounds like their installation of VBA is incomplete".

 

[hedonist]

Thanks guys for all your help. I'll try and sort it out and let you know if I get somewhere.

Cheers

 

[hedonist]

Hi there,

After fiddling around a bit we realised/saw the following:

1) If he/I writes (copies) the code into a new sheet, then IT WORKS!!

2) In Tools| References he didn't have the following selected:
- Microsoft Forms 2.0 Object Library
- Microsoft ActiveX Plugin
(Both have been selected since)

3) In Tools| References he didn't have the following options (at all):
- Microsoft Windows Common Controls 6.0 (SP6)
(C:\WinNT\system32\MSCOMCTL.OCX)
- Microsoft Windows Common Controls-2 6.0 (SP6)
(C:\WinNT\system32\MSCOMCT2.OCX)
but the files are present in the system32 folder.

Any ideas of how to fix this?

 

[BJungheim]

{snip}
3) In Tools| References he didn't have the following options (at all):
- Microsoft Windows Common Controls 6.0 (SP6)
(C:\WinNT\system32\MSCOMCTL.OCX)
- Microsoft Windows Common Controls-2 6.0 (SP6)
(C:\WinNT\system32\MSCOMCT2.OCX)
but the files are present in the system32 folder.

Any ideas of how to fix this?

If you don't have the files at all, you can download them here:
http://www.ascentive.com/support/new/support_dll.phtml?dllname=MSCOMCTL.OCX

Also on that site are the instructions for registering the controls.

 

[hedonist]

Hi BJungheim,

Thanks for all your trouble (and yours Andrew)! Found a working solution from Andrew through another post. Just in case someone else has the same problem, here is the code:

Function Initials(Txt As String) As String
    Dim Arr As Variant
    Dim x As Integer
    Arr = VBA.Split(Txt, " ")
    For x = LBound(Arr) To UBound(Arr)
        Initials = Initials & VBA.Left(Arr(x), 1)
    Next x
End Function

Thanks again!