Debug error on one PC but not another

BigDelGooner

Board Regular
Joined
Aug 17, 2009
Messages
197
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 ;)
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
Try adding this near the start of your code:
Code:
On Error Resume Next
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Aug 17, 2009
Messages
197
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

Board Regular
Joined
Aug 17, 2009
Messages
197

ADVERTISEMENT

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

BigDelGooner

Board Regular
Joined
Aug 17, 2009
Messages
197
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 17, 2009
Messages
197
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,802
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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:
Rich (BB code):

' 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

Board Regular
Joined
Aug 17, 2009
Messages
197
ZVI - you are the man!!!!!!!!!!!!!!!!!! :biggrin:

Thank you very much, that function is perfect!

Thank you all for your help.

Have a good weekend.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top