VBA Help to add and install Excel Add-In

[powercell99]

Morning, I need help using VBA to load and install my macro as an AddIn.

I created a macro to autocorrect room number problems that we've been having in excel because of the way excel interprets the room numbers into Scientific Notation and Dates (ei: 9E23 becomes 9E+23 or 900000000000000000000000, and 6-6049 becomes 6/01/6049 or 1515545. so the macro would be handle for other people in the office, so i saved it as an Add-in. But i want to make the installation and Adding of the Add-in as easy as possible for the other users. I came across a code to install & add the add-in using a procedure, but i dont understand where the code should exist and what triggers it. Any help you could provide would be greatly appreciated.
I am including the macro for the roomnumberfix and also the Add-In installation code i found to automate the installation (but cant figure out how to get it to work.

Code that supposedly automates the installation of the AddIn:

Sub InstallRoomNumFix()
Dim CreateAnAddIn As AddInOn Error GoTo ErrorhandlerSet CreateAnAddIn = AddIns.Add _    (Filename:="MSS_AddIn.xlam")        CreateAnAddIn.Installed = True            MsgBox CreateAnAddIn.Title & " has been installed"        Exit Sub    Errorhandler:        MsgBox "An error has occured"        
End Sub

Code for the Room Number Fix (this is the code that needs to become the addin):

Sub FixRmNum()
Application.OnKey "^l", "FixRmNum"
'created August 2014
Dim LR As LongDim rcell As RangeDim rng As Range
Application.DisplayAlerts = FalseApplication.AskToUpdateLinks = False
Set rng = Selection
Selection.NumberFormat = "@"
For Each rcell In rng
        If Mid(rcell, 2, 2) = "E+" Then    rcell = Left(rcell, 2) & Right(rcell, 2)        ElseIf rcell.Value = 100000000 Then    rcell.Value = "1E08"        ElseIf rcell.Value = 200000000 Then    rcell.Value = "2E08"      ElseIf rcell.Value = 10000000000# Then    rcell.Value = "1E10"         ElseIf rcell.Value = 30000000000# Then    rcell.Value = "3E10"         ElseIf rcell.Value = 50000000000# Then    rcell.Value = "5E10"      ElseIf rcell.Value = 70000000000# Then    rcell.Value = "7E10"           ElseIf rcell.Value = 80000000000# Then    rcell.Value = "8E10"            ElseIf rcell.Value = 90000000000# Then    rcell.Value = "9E10"        ElseIf rcell.Value = 20000000000000# Then    rcell.Value = "2E13"              ElseIf rcell.Value = 2000000000000# Then    rcell.Value = "2E12"              ElseIf rcell.Value = 50000000000000# Then    rcell.Value = "5E13"              ElseIf rcell.Value = 200000000000000# Then    rcell.Value = "2E14"              ElseIf rcell.Value = 1000000000000# Then    rcell.Value = "1E12"              ElseIf rcell.Value = 900000000000000# Then    rcell.Value = "9E14"              ElseIf rcell.Value = 800000000000000# Then    rcell.Value = "8E14"            ElseIf rcell.Value = 500000000000000# Then    rcell.Value = "5E14"            ElseIf rcell.Value = 800000000000000# Then    rcell.Value = "8E14"                ElseIf rcell.Value = 8000000000000# Then    rcell.Value = "8E12"                ElseIf rcell.Value = 10000000000000# Then    rcell.Value = "1E13"               ElseIf rcell.Value = 900000000000# Then    rcell.Value = "9E11"                ElseIf rcell.Value = 800000000000# Then    rcell.Value = "8E11"            ElseIf rcell.Value = "195893" Then    rcell = "5-2436"         ElseIf rcell.Value = "198815" Then    rcell = "5-2444"             ElseIf rcell.Value = "200276" Then    rcell = "5-2448"             ElseIf rcell.Value = "201737" Then    rcell = "5-2452"        ElseIf rcell.Value = "203928" Then    rcell = "5-2458"            ElseIf rcell.Value = "205389" Then    rcell = "5-2462"            ElseIf rcell.Value = "207581" Then    rcell = "5-2468"         ElseIf rcell.Value = "208311" Then    rcell = "5-2470"               ElseIf rcell.Value = "209042" Then    rcell = "5-2472"             ElseIf rcell.Value = "211964" Then    rcell = "5-2480"                 ElseIf rcell.Value = "577206" Then    rcell = "5-3480"         ElseIf rcell.Value = "669278" Then    rcell = "6-3732"             ElseIf rcell.Value = "675821" Then    rcell = "5-3750"         ElseIf rcell.Value = "675852" Then    rcell = "6-3750"             ElseIf rcell.Value = "677313" Then    rcell = "6-3754"            ElseIf rcell.Value = "697370" Then    rcell = "5-3809"            ElseIf rcell.Value = "699927" Then    rcell = "5-3816"         ElseIf rcell.Value = "708693" Then    rcell = "5-3840"             ElseIf rcell.Value = "717459" Then    rcell = "5-3864"
    ElseIf rcell.Value = "726225" Then    rcell = "5-3888"             ElseIf rcell.Value = "742295" Then    rcell = "5-3932"         ElseIf rcell.Value = "745217" Then    rcell = "5-3940"             ElseIf rcell.Value = "745947" Then    rcell = "5-3942"             ElseIf rcell.Value = "748869" Then    rcell = "5-3950"             ElseIf rcell.Value = "815679" Then    rcell = "4-4133"             ElseIf rcell.Value = "1330700" Then    rcell = "5-5543"             ElseIf rcell.Value = "1338005" Then    rcell = "5-5563"             ElseIf rcell.Value = "1571791" Then    rcell = "6-6203"         ElseIf rcell.Value = "1572887" Then    rcell = "6-6209"            Else
     End If    Next rcell
      
Range("A1").Select
Application.DisplayAlerts = True

MsgBox "The ""Room-Number-Corrector"" has corrected the Room Numbers for you!", vbInformation     End Sub

 

[CharlesChuckieCharles]

Why not format the Room Number cells as text and you wouldn't need this

Or make the macro change the format of the cell to text and replace the "+" with "" using Replace

Job Done

 

[powercell99]

thanks for the suggestion but its more complicated than that. We tried just changing the format to text and it didn't solve the entire problem. Most of the room number then can be corrected (after changing it to text) by replacing the "E+", but many cells don't translate to text as the "E+" text format , instead they are seen as 9E00000000000 (for 9E+11, or 9E11 room number), AND many room numbers are seen in text as numerical date value for what excel is interpreting as a date (room number 6-6049 is seen as 1515545). So simply changing the format to text and replacing the "E+" doesn't solve the entire problem. Even if it did, I'd still want a macro to accomplish that to assist the people running the reports - and I'd want that macro saved as an Add-In. So my questions isnt necessarily about how to correct the room numbers , i need help in making the macro/add-in as easy to install as possible on co-workers PCs.

Any suggestions would be greatly appreciated. Thanks

 

[CharlesChuckieCharles]

I mean change the format where people input the Room numbers solve it at source