Variable Not Defind - Please help Masters of VBA

[hehanhan]

Hello masters,

I am experecing this erorr "vaiable not defined", it is highlighted with shUSB. I tired to set this worksheet. but I failed with multiple tries.
Please help and let me know where is wrong. thank you so much

Private Sub AUTOLOCATE_Click()
Dim strComputer     As String
    Dim strDeviceName   As String
    Dim objWMIService   As Object
    Dim colControllers  As Object
    Dim objController   As Object
    Dim colUSBDevices   As Object
    Dim objUSBDevice    As Object
    Dim i               As Integer
    Dim wk As Workbook
    Dim ws As Worksheet
    ActiveWorkbook.Sheets("shUSB").Select

    'Just in case of an error...
    On Error Resume Next
    
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Clear the sheet (except headings).
    shUSB.Range("C2:G30").ClearContents
    
    'Set the computer.
    strComputer = "."
    
    'The root\cimv2 namespace is used to access the Win32_USBControllerDevice class.
    Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
    
    'A select query is used to get the list of all USB controllers.
    Set colControllers = objWMIService.ExecQuery("Select * From Win32_USBControllerDevice")
    
    'Start below sheet headings.
    i = 2
    
    'Loop through all the collection of USB controllers.
    For Each objController In colControllers
       
       'Retrieve the device name from the controller.
      strDeviceName = Replace(objController.Dependent, Chr(34), "")
       strDeviceName = Right(strDeviceName, Len(strDeviceName) - WorksheetFunction.Find("=", strDeviceName))
       
       'Execute a select query on Win32_PnPEntity class based on device name.
       Set colUSBDevices = objWMIService.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '" & strDeviceName & "'")
       
       'Loop through all the USB devices and write the necessary data in the sheet.
       For Each objUSBDevice In colUSBDevices
            With shUSB
                .Cells(i, 2).Value = objUSBDevice.Name
                .Cells(i, 3).Value = objUSBDevice.Manufacturer
                .Cells(i, 4).Value = objUSBDevice.Status
                .Cells(i, 5).Value = objUSBDevice.Service
                .Cells(i, 6).Value = objUSBDevice.DeviceID
            End With
            i = i + 1
        Next
    Next
    
    'Adjust columns width.
    shUSB.Columns("C:G").AutoFit
'Inform the user about the process.
    MsgBox "COM PORT # Located Successfully!", vbInformation, "Finished"


End Sub

 

[Trebor76]

Hi hehanhan,

You need a shUSB worksheet variable like so...

Dim shUSB As Worksheet

...and then you need to set it like so:

Set shUSB = ActiveWorkbook.Sheets("shUSB")

You then can delete or comment out the line that selects the shUSB tab.

HTH

Robert

 

[hehanhan]

Thank you so much. It worked fine after the change.

Hi hehanhan,

You need a shUSB worksheet variable like so...

Dim shUSB As Worksheet

...and then you need to set it like so:

Set shUSB = ActiveWorkbook.Sheets("shUSB")

You then can delete or comment out the line that selects the shUSB tab.

HTH

Robert

 

[Trebor76]

Thanks for letting us know and you're welcome