Debug error on one PC but not another

B

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 ;)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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
 
Upvote 0
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).
 
Upvote 0
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?!
 
Upvote 0
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!!
 
Upvote 0
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?
 
Upvote 0
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?!
 
Upvote 0
BigDelGooner said:
Can I remove the reference file at user level some way (I've tried doing this manually and it wont let me uncheck it)?
Click to expand...
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
 
Upvote 0
ZVI - you are the man!!!!!!!!!!!!!!!!!! :biggrin:

Thank you very much, that function is perfect!

Thank you all for your help.

Have a good weekend.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
518
picklefactory
P
JeffGrant
  • Solved
Excel talking to Outlook throws 462 errors
Replies
7
Views
342
JeffGrant
JeffGrant
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
95
RoryA
RoryA
P
  • Question Question
Run-Time error '-2147467259 (80004005)': Method 'InsertPictureInCell' of object 'Range' failed
Replies
3
Views
330
PopeScooby
P
MrIfOnly
  • Question Question
shapes.onaction in add-in using reference path to macro (Excel VBA)
Replies
8
Views
134
jkpieterse
jkpieterse

Forum statistics

Threads
1,224,616
Messages
6,179,910
Members
452,949
Latest member
beartooth91

Share this page

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
Back
Top