Function or sub (add-in)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arne Hegefors - 07 Dec 2007 07:59 GMT

Hi! I have a very easy sub that I use in many worksheets. I use the sub like
this: I select certain cells and then I press Alt+F8. The sub then removes
blanks that are used as thousand separators (1 000 000 becomes 1000000) so
that Excel can read the values as numbers and not as text. I need to use this
because I paste data from another program into Excel. Now I want to be able
to use this in all my workbooks. I know how to make an add-in for FUNCTIONS.
The problem is I dont know how to run my sub when saving it as an add-in. Do
I have to use a function instead? Is it possible to rewrite my sub into a
function instead? I woulöd very much appreciate help with this. Thanks alot!
Code for my sub:

Sub Makro1()
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Dec 2007 08:41 GMT

Unless I am missing something... do you really need to use a macro for this?
What about selecting the cells the way you do now, press Ctrl+H (or click on
Edit/Replace on Excel's menu bar), type a single space character (this will
go into the selected first "Find What" field and will stay there for any
subsequent calls to this dialog box unless replaced by a subsequent search)
and then press Alt+A (or click the Replace All button). Press the Enter key
to remove the MessageBox that appears and then the Esc key to close down the
dialog box (or click the appropriate buttons).

Rick

> Hi! I have a very easy sub that I use in many worksheets. I use the sub
> like
[quoted text clipped - 18 lines]
> ReplaceFormat:=False
> End Sub

Reply to this Message

Arne Hegefors - 07 Dec 2007 08:54 GMT

Hi Rick! Thanks for your answer. You are quite right in that I can use the
way that you described. The problem is that it is just one example of very
many such procedures that I have to do. Thus any manul solution will be too
time consuming. I have tried to figure out how to fix this by using an add-in
(which seems reasonable to me at least..) but so far I have not managed to
get it to work. Can you please answer me IFT IS POSSIBLE or am I just wasting
my time here? I need to make a reference to a selection (which can be a
problem using add-ins as I understand it) and I need to have it as a sub (or
is it possible to do changes like that using a function) or at least
sometihng so I can run it when I want. I would very much appreciate help with
this. Again many thanks for your answer.

"Rick Rothstein (MVP - VB)" skrev:

> Unless I am missing something... do you really need to use a macro for this?
> What about selecting the cells the way you do now, press Ctrl+H (or click on
[quoted text clipped - 29 lines]
> > ReplaceFormat:=False
> > End Sub

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Dec 2007 09:18 GMT

Perhaps this will be of some help...

http://office.microsoft.com/en-us/excel/HA010872961033.aspx

Rick

> Hi Rick! Thanks for your answer. You are quite right in that I can use the
> way that you described. The problem is that it is just one example of very
[quoted text clipped - 59 lines]
>> > ReplaceFormat:=False
>> > End Sub

Reply to this Message