-1

I wrote an VBA script in an Excel Macro. I basically collects data from the rows and posts it to a RESTful service. It works in all our machines except for one where nothing happens when we click on the buttons. There are no errors/messages of any kind. The code resides in the Microsoft Excel Objects(under corresponding sheets). I have copied the code gist below:

Const url As String = "https://xx.yy.com"
Const token As String = "aaaa"

Private Sub CommandButton1_Click()
 LoadXX
End Sub


Function LoadXX()

    Dim xChild1, xChild As MSXML2.IXMLDOMNode
    Dim zipResult As New MSXML2.DOMDocument
    Dim zipService As New MSXML2.XMLHttp
    Randomize       
    random_number = Int(99999999 * Rnd) + 1
    Query = url & "callService?sessionId=" & random_number      
    zipService.Open "GET", Query, False
    zipService.setRequestHeader "X-Access-Token", token
    On Error GoTo ThereWasBadConnection
    zipService.send
    zipResult.LoadXML (zipService.responseText)
End Function

I have done the usual steps such as:

  1. Choosing Enable all Macros in Trust Center settings
  2. Enabling Trust access to the VBA project object model in Trust center settings
  3. Choosing 'Run all from my Computer' in Control Panel -> Programs and Features -> Microsoft Office 2010
  4. Reinstalling Office 2010 a couple of times.

I have even got Admin rights on my machine. Please let me know if any further details are needed.

Help!

Edit

I have to say that this is my first coding experience in Excel macro(VBA). I have written my code inside corresponding sheets inside Microsoft Excel Objects(MEO). If I move it to Modules it works. In a lot of machines it(code inside MEO) still works fine. Why this behaviour?

Community
  • 1
  • 1
user2085546
  • 21
  • 3
  • please, can those who down-vote leave some comment, as it's obvious for us why it's 'deserve' to be down-voted, it might not so obvious for new user. – Abie Giordano Jan 06 '15 at 08:16
  • The first two steps for allowing troubleshooting here: 1) please post your code (a minimized sample of it); 2) please define "doesnt work", because it can mean either it crashes, or it does nothing, or something else. Thank you. – Matteo NNZ Jan 06 '15 at 09:36
  • Thanks for the feedback. I ve added code and defined what I meant by not working. – user2085546 Jan 06 '15 at 09:56
  • Perhaps you are a victim of [this Microsoft Update](http://stackoverflow.com/q/27411399/445425) – chris neilsen Jan 08 '15 at 07:16
  • Thanks a lot @chrisneilsen !! Yes, This MS update is the cause. I recompiled the code in the machines where it did not work and it works fine now. – user2085546 Jan 13 '15 at 04:44

1 Answers1

1

The problem was because of the Microsoft Security update as shared by @chrisneisen in the comments.

We had two categories: machines on which the macro worked(abbreviated to WM) and the machine on which it did not(abbreviated to NWM). The solution is to recompile the VB code on the NWM.

I tried to recompile the code on the NWM but excel kept crashing when I tried to do so. So, I made a copy of the worksheet, renamed the original worksheet to a temp name and renamed the newly copied worksheet to the original name. Now the macro in the newly copied worksheet worked(in both WM and NWM). When I tried to delete the original worksheet, excel crashed again so I decided to simply hide it.

user2085546
  • 21
  • 3