Error 400 Solution Required

Hi All

Hope someone can help with a problem running a macro.

I am using Excel version 2010 and have been running an excel macro without any problems.

I then transfered the spreadsheet and ran it in Excel version 2007 without any problems.

1 week later the version 2007 is now throwing an error 400 when it is run. I transfered a copy back to 2010 and it works perfectly.

Had a quick look on the internet to see if it could be easily sorted, but I am unsure how to proceed. The article is saying that this problem is related to an add-in.

Many thanks



Diane

Many thanks for your reply Izandol

It was a code which MJMetzer kindly wrote for me last week. It has run many times without issue, and has just started to be a problem today.

It is on a laptop which is being used by a true novice when it comes to excel, so I am thinking the settings have been changed in some way.

The error message - Microsoft Visual Basic X 400 but does not point to any line within the code.

Macro For Dividing a Named Range by 10 was the original thread.

Just tried to run a different macro and the same error appears.

Thanks


Diane

Hi Izandol

I have loaded Excel in "Safe Mode", and yes the macro runs perfectly.

Diane

Hi Izandol

Thanks for your reply.

I have checked the Excel Add-ins (Excel Options, Manage Excel Add-ins, Go) and I have no add-ins sellected.

Could it be an add-in which needs to be added.

Thanks


Diane

Thanks Izandol

I will check the COM add-ins. But as for the 2 XLSTART folders.....where will I find them?

Kindest regards

Diane

Thanks

Will do.

Hi Diane,

I hope you have the problem fixed. If not, you might want to try the following modified version of the code that causes your problem on the offending computer. It uses 'Conditional Compilation' in an attempt to isolate the problem if it is in fact my Macro that causes the problem.

Instructions are inside the Macro. The aim is to start with everything disabled, and run the Macro. One by One by setting values to 'True', small parts of the code are enabled one by one. The Macro won't do it's intended function, but the aim is not to run the Macro, but to attempt to isolate the 400 error.

Lewis

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following item defines where the 'Sentinel Cell' is to go.
'When the cell is BLANK, then division is allowed.
'After division is performed, the Macro puts a value in the cell that prevents future division.
'
'You can make this cell hidden by using custom format of three semi-colons (;;;)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#Const IMPLEMENT_SECTION_A = False
#If IMPLEMENT_SECTION_A = True Then
Const sSheetName = "TestData"
Const sDivisionAllowedSentinel_CELL = "A24"
#End If

Sub DivideContentsOfEntireNamedRangeByTenOnlyOnceConditionallyCompiled()
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'This divides the Entire Contents of range 'TempData' by 10
  'The sentinel cell defined in the beginning allows division to occur only once
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
'The '#If' statements are called 'CONDITIONAL COMPILATION'.  When all the values are
''False' then the routine is a blank shell and does nothing (none of the code is compiled).
'
'The code is standard VBA code and should not cause your 400 error.  This is an attempt to
'isolate the problem if it is causesd by something in my code.
'
'If you run the code as is,  it is an empty shell and does nothing.  If the 400 problem occurs,
'them the 400 problem should occur with any other macro also.
'
'By setting the values to 'True' one by one starting at 'IMPLEMENT_SECTION_A', we are attempting
'to isolate the problem to one section of code.
  
  
#Const IMPLEMENT_SECTION_B = False
#If IMPLEMENT_SECTION_B = True Then
  Dim r As Variant    'r is each cell in the range
  Dim myRange As Range
  
  Dim sSentinelValue As String
  MsgBox "Conditional Compilation Version"
#End If

#Const IMPLEMENT_SECTION_C = False
#If IMPLEMENT_SECTION_C = True Then
  'All the calculations are to be done on the "TestData" Sheet
  Sheets("TestData").Select
#End If
  
#Const IMPLEMENT_SECTION_D = False
#If IMPLEMENT_SECTION_D = True Then
  'Get the value from the Sentinel Cell (Blank means Ok to divide/ anything else means DO NOT DIVIDE)
  sSentinelValue = Trim(Range(sDivisionAllowedSentinel_CELL).Text)
  If Len(sSentinelValue) > 0 Then
    MsgBox "TERMINATING.  Division Not Allowed." & vbCrLf & _
           "Division already occurred according to the value in Cell '" & sDivisionAllowedSentinel_CELL & "'."
    Exit Sub
  End If
#End If
  
#Const IMPLEMENT_SECTION_E = False
#If IMPLEMENT_SECTION_E = True Then
  'Assign the range object
  Set myRange = Range("TempData")
#End If
  
  
#Const IMPLEMENT_SECTION_F = False
#If IMPLEMENT_SECTION_F = True Then
  'Loop thru the range
  'Divide all non-zero values by 10
  For Each r In myRange
    'Debug.Print r.Address(False, False)
    If r.Value <> 0 Then
      r.Value = r.Value / 10#
    End If
  Next r
#End If
  
#Const IMPLEMENT_SECTION_G = False
#If IMPLEMENT_SECTION_G = True Then
  'Set the Sentinel Cell
  Range(sDivisionAllowedSentinel_CELL).Value = "Divided by 10 on " & Now()
#End If
  
#Const IMPLEMENT_SECTION_H = False
#If IMPLEMENT_SECTION_H = True Then
  'Clear the object
  Set myRange = Nothing
#End If

End Sub

Hi Lewis

Very sorry for the late reply......not enough hours in the day sometimes.

I have not been able to rectify the problem at the moment, and will certainly run the code and let you know the results.

Once again Lewis your time and expertise is very much appreciated.

Diane