Finding Variables not declared in VBA code

[CheekyDevil]

Hi experts,

I was trawling through countless pages of Google seeking an answer to this, with no success.

I have a VBA project throwing a "Runtime91" error advising a variable has not been declared, but doesn't tell me what variable. In Debug, it refers me to a single line of code that shows my userform on a command button press.

The only other indication I've had is the following codes "NumberFormat" but this should not be a variable, simply how the date and time is to be formatted when entered.

I've got OE at the top and still no joy.

Is there any code I can implement or any error checking options in VBA that will point me to the variable not declared?

Here's the code piece I'm referring to above:

Option Explicit
Dim ws As Worksheet
Dim LastRow As Long


Private Sub UserForm_Initialize()


  Set ws = Sheet9

'Focus Log
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Adds Date
    ws.Range("A" & LastRow).Value = Date
    NumberFormat = "dd/mm/yyyy"
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Adds Time
    ws.Range("B" & LastRow).Value = Time
    NumberFormat = "hh:mm:ss"
    LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row + 1 'Adds Username
    ws.Range("C" & LastRow).Value = Environ("username")

 

[Michael M]

You haven't declared where the number format will be applied !
Also, I'd probably use

Set ws=sheets("Sheet9")

or

Set ws=sheet(9)

With ws
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 'Adds Date
    .Range("A" & LastRow).Value = Date
    .Range("A" & LastRow).NumberFormat = "dd/mm/yyyy"
    LastRow = .Range("B" & Rows.Count).End(xlUp).Row + 1 'Adds Time
    .Range("B" & LastRow).Value = Time
    .Range("B" & LastRow).NumberFormat = "hh:mm:ss"
    LastRow = .Range("C" & Rows.Count).End(xlUp).Row + 1 'Adds Username
    .Range("C" & LastRow).Value = Environ("username")
    End With

 

[CheekyDevil]

WOW was I THAT blind! Thank you SO much Michael! You have saved me hours of frustration in one swoop, and so quickly! Had not even seen this line was written incorrectly.

Forever grateful for your help and I'll now go back to VBA school to learn the basics again :P

Thanks again!

 

[Michael M]

Happens to us all...in my case it's called seniors moments !!...

 

[Fluff]

Also, in the VB Editor, if you select Tools > Options > General > & select "Break in Class Module"
Rather than showing you the Userform.show line, it will highlight the problem within the initialise event.