strange Solver traces

[jrtaylor]

How does one find and delete traces of of Solver data?

I use Solver frequently. A few days ago I decided to consolidate my primary workbook (which has about 200,000 cells) in hopes of making it more efficient for Solver.

So I did a "cut/paste" in which I removed my solver "input cells" (the variables which Solver will use in its operation) and pasted them into an empty area of another worksheet.

I knew of course that I'd need to re-enter the constraints etc. in Solver after doing this. But when I input some constraints, Solver somehow over-wrote my formulas with its own code. Here's a screenshot of what it did. Note the last two rows and you'll see the unexpected code:

https://www.dropbox.com/s/ns4bd6w2n6...01.16.png?dl=0

I deleted the code, re-entered the constraints as usual, but again, Solver changed my entries to this code. So I took a number of additional steps including:

- Doing a "reset" of Solver to clear everything from it's interface.
- Deleting Solver from add-ins
- Rebooting my computer
- Saving as different file
- Doing an "Online Repair" of Office 365

In all cases, Solver continued to insert the code (which prompts an error message when trying to solve).

So... does anyone know where Solver data is hidden, how to access it, clean it up? Copying/Pasting the entire workbook would be the very last step for me, as I'm afraid that this is going to require that I search for and replace hundreds of parts of formulas which refer back to the original workbook.

Thanks for your help... and Moderators, if I should repost this in a sub-forum elsewhere I'll do so.

[AliGW]

We'll leave it here for now. As always, please attach the workbook directly here: many of us are unwilling or unable to access file-sharing sites. Thanks!

[jrtaylor]

Thanks. I had tried uploading the screenshot as an image but had no success. However I'm trying again, here, where the strange code is visible in the bottom rows. This is after completely "cleaning" the empty space, removing Solver, saving as another file, inserting fresh data into the inputs, and opening/editing the file on a different computer with its own excel program:

solver.png

As for the workbook I have signed a NDA and cannot make it public. However I can describe or replicate aspects of it as necessary.

I appreciate your help!

[AliGW]

Moved to VBA forum.

[Alf]

Working with matrices a concept that pops up is "the left hand side" or "the right hand side" abbreviated "lhs" and "rhs" so I would assume this code is part of the internal workings of solver and should not be visible.

As for help I think your best chance in solving this problem is to contact the makers of "Solver" and hopefully their help desk may solve your problem.

https://www.solver.com/

Could you please post back any information/ feed back you get as this should be of general interest to forum members.

Alf

[jrtaylor]

Hi Alf,

I ran the file inspector which led to this article:

https://support.office.com/en-us/art...rs=en-US&ad=US

Which directs to this website which has the VBA code:

https://support.microsoft.com/en-us/...ctive-workbook


I created the macro and ran it. I get this window and click "yes":

Screenshot 2017-08-11 11.04.58.png

And then this window opens:

Screenshot 2017-08-11 11.00.40.png

It highlights this piece of code:

xName.Delete

Here's the full code, pasted from the Microsoft website:

  Sub Remove_Hidden_Names()

       ' Dimension variables.
       Dim xName As Variant
       Dim Result As Variant
       Dim Vis As Variant

       ' Loop once for each name in the workbook.
       For Each xName In ActiveWorkbook.Names

           'If a name is not visible (it is hidden)...
           If xName.Visible = True Then
               Vis = "Visible"
           Else
               Vis = "Hidden"
           End If

           ' ...ask whether or not to delete the name.
           Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
               Chr(10) & xName.Name & "?" & Chr(10) & _
               "Which refers to: " & Chr(10) & xName.RefersTo, _
               Buttons:=vbYesNo)

           ' If the result is true, then delete the name.
           If Result = vbYes Then xName.Delete

           ' Loop to the next name.
       Next xName

   End Sub

Any advice? Thanks!

[shg]

Names like that are created when you open a 2007+ workbook with newer functions with earlier versions of Excel (e.g., 2003) with the compatibility pack installed, but without the trailing question mark -- dunno where that came from.

You might search the workbook for _xlfn and fix any formulas that contain it.

Regarding deleting those names, you might run this to unhide them and then see if you can delete manually:

Sub UnhideNames()
  Dim oNam          As Name
  
  For Each oNam In ActiveWorkbook.Names
    oNam.Visible = True
  Next oNam
End Sub

[jrtaylor]

Hi, thanks! I ran it, then opened Name Manager and found a few dozen solver-generated names. So will delete them and start over.

Appreciate your help very much!

[shg]

You're welcome.