Solved

Run-time error '5' Excel VBA Script

Posted on 2014-04-17
3
506 Views
Last Modified: 2014-05-03
I have written a script to go through vulns and determine the application the vuln applies to. But i get a run-time errors and not sure why. Here is the code.
Sub Format()
    
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    displayPageBreakState = ActiveSheet.DisplayPageBreaks
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    displayPageBreakState = ActiveSheet.DisplayPageBreaks
    
    Dim re As New RegExp
    
    re.Pattern = "MS[0-9][0-9]-[0-9][0-9][0-9]"
    re.IgnoreCase = True
    re.Global = True
    
    Sheetcount = ThisWorkbook.Worksheets.Count
    For N = 1 To Sheetcount
    'For N = 1 To 1
        Sheets(N).Select
        
        ActiveSheet.DisplayPageBreaks = False
        
        Worksheets(N).Range("H1").Value = "Vulnerability Type"
        Columns("H:H").EntireColumn.AutoFit
        Worksheets(N).Range("I1").Value = "Microsoft Severity"
        Columns("I:I").EntireColumn.AutoFit
        'Worksheets(N).Range("J1").Value = "CVE Severity"
        'Columns("J:J").EntireColumn.AutoFit
        numrows = Worksheets(N).UsedRange.Rows.Count
        'For C = 2 To numrows
        For C = 1590 To numrows
            If InStr(Range("D" & C).Value, "jre-") Then
                Range("H" & C).Value = "Java"
            ElseIf InStr(Range("D" & C).Value, "adobe-") Then
                Range("H" & C).Value = "Adobe"
            ElseIf InStr(Range("D" & C).Value, "oracle-") Then
                Range("H" & C).Value = "Oracle"
            ElseIf InStr(Range("D" & C).Value, "mfsa") Then
                Range("H" & C).Value = "Mozilla"
            ElseIf InStr(Range("D" & C).Value, "windows-") Then
                Range("H" & C).Value = "Windows"
            ElseIf Range("D" & C).Value = "" Then
                Range("H" & C).Value = ""
            Else
                Range("H" & C).Value = "Other"
            End If
            Set allMatches = re.Execute(Range("D" & C).Value)
            If allMatches.Count > 0 Then
                tempString = getMsSeverity(Left((Range("G" & C).Value), 8))
                If InStr(tempString, "Critical") Or InStr(tempString, "Important") Or InStr(tempString, "Moderate") Or InStr(tempString, "Low") Then
                    Range("I" & C).Value = Mid(tempString, 40, InStr(40, tempString, ":") - 41)
                Else
                    Range("I" & C).Value = "No Severity"
                End If
            End If
            'If Range("E" & C).Value <> "" Then
            '    Range("J" & C).Value = getCVESeverity(Left((Range("E" & C).Value), 13))
            'End If
        Next C
        
        ActiveSheet.DisplayPageBreaks = displayPageBreaksState
        
    Next N
    
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
    
End Sub

Function getMsSeverity(MS) As String
    Dim title As String
    Dim objHttp As Object
    
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET", "http://technet.microsoft.com/en-us/security/bulletin/" & MS, False
    objHttp.Send ""

    title = objHttp.ResponseText

    If InStr(1, UCase(title), "<TITLE>") Then
        title = Mid(title, InStr(1, UCase(title), "<TITLE>") + Len("<TITLE>"))
        title = Mid(title, 1, InStr(1, UCase(title), "</TITLE>") - 1)
    Else
        title = ""
    End If

    getMsSeverity = title
End Function

Function getCVESeverity(CVE) As String
    Dim title As String
    Dim objHttp As Object
    
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET", "http://web.nvd.nist.gov/view/vuln/detail?vulnId=" & CVE, False
    objHttp.Send ""

    title = objHttp.ResponseText

    If InStr(1, title, "<span class=""label"">CVSS v2 Base Score:</span>") Then
        title = Mid(title, InStr(1, title, "<span class=""label"">CVSS v2 Base Score:</span>"))
        title = Mid(title, 1, InStr(1, title, "</a>") - 1)
        title = Right(title, Len(title) - InStrRev(title, ">"))
    Else
        title = ""
    End If
    
    getCVESeverity = title
End Function

Open in new window

0
Comment
Question by:evilmonkey2148
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Expert Comment

by:bigeven2002
ID: 40007324
Hello,
We have seen the same error for one of our applications.  Not sure if it is relevant, but try changing your default printer to another (preferably local non-network) and then change it back to see if that works.
0
 

Accepted Solution

by:
evilmonkey2148 earned 0 total points
ID: 40027568
This was due to poor programming. Made the program more dynamic but pulling the needed information instead of static variables.
0
 

Author Closing Comment

by:evilmonkey2148
ID: 40039030
didn't do enough code review before submitting the question.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question