Solved

Run-time error '5' Excel VBA Script

Posted on 2014-04-17
3
495 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
  • 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now