Debug error on one PC but not another

[BigDelGooner]

I am getting a frustating error here at work...I have created a spreadsheet that runs fine on my machine but not another.

The specific line of code that appears to be stumbling on is...

If eomonth(Range("Date_To"), -Range("Period_DownMonths")) < Sheets("Monthly_Returns").Cells(6, 1).Value Then
' CODE HERE
End if

I have looked under references within VBA for 'MISSING' files on the suspect machine and there is one. It relates to the excel AddIn from some financial data software I have on my machine that isn't on the suspect machine...but as you will see above the line of code (or any other part of that macro) is nothing to do with this software!? Is this anything to do with it, or just a red herring?

Any ideas how I can get round this? HELP ME

 

[JackDanIce]

Try adding this near the start of your code:

On Error Resume Next
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True

 

[MrKowz]

EOMONTH is a formula not native to Excel. You need to enable the Analysis Toolpak - VBA for that computer. You can do this by going to Tools>Add-Ins (in Excel, not VBA).

 

[BigDelGooner]

Thank you for your replies.

Sorry in my first post I forgot to mention...I have this code on the workbook open command.

Here is exactly what I have...

Private Sub Workbook_Open()
If Application.AddIns("Analysis Toolpak").Installed = False Then
Application.AddIns("Analysis Toolpak").Installed = True
End If

If Application.AddIns("Analysis Toolpak - VBA").Installed = False Then
Application.AddIns("Analysis Toolpak - VBA").Installed = True
End If
End Sub

What else could it be?!

 

[BigDelGooner]

I also manually checked if they had been installed and they had...

 

[BigDelGooner]

Right I have narrowed down the issue but not sure how I can resolve it...

Basically the MISSING Reference file I am talking about relates to the financial software I am using when I update the underlying data using my machine.

However when I send the workbook to another user who does not have the financial software the code is falling over at a random points...even though the points it falls over has nothing to do with the Reference file from the financial software (i.e. the IF statement I posted above).

Looking around the internet it appears that if a file is MISSING then it can cause this kind of strange behaviour. So basically my question is...how can I avoid this issue? Can I remove the reference file at user level some way (I've tried doing this manually and it wont let me uncheck it)? If I remove the reference at user level will it let me update the underlying data using the financial software on my machine at a later date..?

Failing this...it is also running other parts of my code but falling over on the code above. Is it the EOMONTH element? If so is there a way of getting round this?

PLEASE HELP!!

 

[MrKowz]

Since your code is erroring in other areas, I think it is time for us to see the entire code. Can you please post it?

 

[BigDelGooner]

Could be a bit difficult mate. There are about 15 worksheets & 5 modules with lots of separate macros. But anyway I don't think I was clear earlier...the code is only falling over on the IF statement with the EOMOMTH function I have posted above. The more I think about it the more I think it's something to do with the EOMONTH function not being a native function & the MISSING reference file combo?!

 

[ZVI]

Can I remove the reference file at user level some way (I've tried doing this manually and it wont let me uncheck it)?

To uncheck reference to your financial data software:
1. Load workbook with disabled macros (set high level of Excel macro security).
2. VBE - Tools - References - uncheck the reference
3. Save workbook (Ctrl-S)

Instead of Analysis ToolPak’s EOMONTH function use the same one:

' The same function as EOMONTH from Analysis ToolPak add-in
' Reason: exclude compatibility issue
Function MyEomonth(StartDate As Date, Months) As Date
  MyEomonth = DateSerial(Year(StartDate), Month(StartDate) + 1 + Fix(Months), 0)
End Function

 

[BigDelGooner]

ZVI - you are the man!!!!!!!!!!!!!!!!!!

Thank you very much, that function is perfect!

Thank you all for your help.

Have a good weekend.