Problem with codes on different machines

[sujittalukde]

I have written a macro which runs well in the where I have made it.
Now when the same file/macro is run on another machine, it is showing an error at three places:
1. Msgbox at Chr13
My code is given below:

MsgBox ("You must have at lease two YES under Column AS titled COMPUTATION REQUIRED to continue for " & _
"Computation of Tax." & Chr$(13) & Chr$(13) _
& "In case you have only one employee, add a dummy employee and put YES to continue " & _
"for computation."), vbInformation, "Two employees!!!!"

The code runs well on the computer where
I have made it but shows an error at the bold Chr$(13)

"Computation of Tax." & Chr$(13) & Chr$(13)

Error is:
Compile error
Can?t find project or Library.
Why this is happening?

2. Error on User Name:

I am using the following code before the WB close event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Company data").Range("B8").Value = Now
Sheets("Company data").Range("B9").Value = Environ("UserName")
End Sub

Error is coming here:
Environ
Error is:
Compile error
Can?t find project or Library
Why this is happening?

3. I am using the following code to install Analysis Toolpak Add in if not installed:

Sub InstalAddin()
   If AddIns("Analysis ToolPak").Installed = False Then
       AddIns("Analysis ToolPak").Installed = True
   End If
End Sub

How ever while running the file on enabling macro, it showed an error . Then I closed the workbook and opened a new blank Wb and then gone to tools\Addins\ Ticked Analysis Tool Pak. Then the Excel asked ?This feature is not installed on your machine. Would you like to installed it now?
I clicked ?Yes? and then Excel installed the addin and then no error came on opening the workbook?
Why this has happened and how to check this?

Thanks in advance for the valuable advice.

[rory]

The references you set in a workbook are stored with that workbook. If they are not available on the machine on which you try to run the code, you will get errors such as those you mention. (Unhelpfully, the errors usually refer to built-in VBA functions rather than functions in the missing referenced library.)
Once you resolve the reference issue, your code should be fine, but that is obviously tricky where a required feature is not even installed on the other machine!

[tstav]

This is a test post to sujittalukde's thread checking that it's working properly.

[sujittalukde]

Now this thread is working. Thanks.
Rory,
I tried the Environ function separately on the new machine with this code:

Sub test()
a = Environ("Username")
msgbox a
End Sub

This is working well but not with the code mentioned on my first post.
viz;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Company data").Range("B8").Value = Now
Sheets("Company data").Range("B9").Value = Environ("UserName")
End Sub

[mdmackillop]

You can get code to add references if they are available on the other PCs.
Have a look at this KB item

[rory]

What error are you getting?

[tstav]

Might also prove helpful for missing references (no code involved here)
http://www.cpearson.com/excel/MissingReferences.aspx

[sujittalukde]

When Ia ma running Environ function with msgbox, it is woking.
But when I am working with WB before close event , it is not working. It gives error as:
Compile error
Cannot find project or Library.

No such error is coming for msgbox function.

[rory]

It sounds to me like you are still missing a reference (and probably testing the MsgBox version in a different workbook). CHeck Tools->References in the VBEditor and look for checked items that start with 'MISSING:'

[Simon Lloyd]

It is probably faulting because:

[VBA]

"Computation of Tax." & Chr$(13) & Chr$(13)

[/VBA]should look like

[VBA]

"Computation of Tax." & Chr(13) & Chr(13)

[/VBA]you can also use

[VBA]

"Computation of Tax." & Chr(13) & Chr(10)

[/VBA]or

[VBA]

"Computation of Tax." & vblf & vblf

[/VBA]

[Simon Lloyd]

You could also use:
[VBA]
Sheets("Company data").Range("B9").Value Application.Username
[/VBA]

[rory]

But Application.Username and Environ("username") are not necessarily the same thing. The former is the name entered under Tools->Options in Excel, whereas the latter is the Windows login name.

[Simon Lloyd]

I understand that Rory i was just giving alternatives

[sujittalukde]

yes, when Ia m running the environ on a new workbook, it is working but not on the present WB. While going through the Tools/Reference I have found one missing reference but could not understand what is this and that Application.username also not work.
Missing, microsoft windows common control,- 2.6.0 (SP6)
What is this how to rectify it?

[rory]

Do you have any userforms in the workbook with non-standard controls on them? If not, try unchecking the reference, then press Debug->Compile and see if the code is OK?

[sujittalukde]

Thanks rory, its working now. I have unchecked the Missing reference and then both Environ & Application.Username is working. Also no error on msgbox Chr$(13).
But can you please tell me why this has happened and how to trap these type of errors, if possible?

[rory]

When the workbook was being created, at some point it had a non-standard control added to it (or the Reference was added manually). This reference gets saved with the workbook. If you then try and use the workbook on a machine that does not have the required controls or libraries on it, you will get this type of error. It is generally good practice to design a workbook in the earliest version of Excel that you expect it to be used with and to ensure, if possible, that any additional controls/libraries you use will be available on each machine.