VBA Sub not starting (but compiling)

[saboh12617]

Hello,

I feel very clueless because i wrote this simple Macro but i can not run it (F5/F8). The compiler however signals no error:

For information Total1 is a Worksheet, "tblFiles9" a structured table, and "GetGenericName" a simple function that poses no problem in other subs (it just returns a string concatenation).

Did i use a incompatible variable name? Thanks you in advance for any advices.

Public Sub UGN()
  ' 1 reference to "Total" sheet for generic values to use
  '
  
  Dim i As Long, countI As Long
  Dim newNames() As String
  ReDim newNames(1 To 15, 1 To 1)
  
  
  With Total1.ListObjects("tblFiles9").DataBodyRange
    
    
    For i = 1 To .Rows.count
      If .Cells(i, 1).Value2 Then
        countI = countI + 1
        With Total1.Range("f5:f8")
          newNames(i, 1) = GetGenericName( _
                           .Cells(1, 1).Value2, _
                           .Cells(2, 1).Value2, _
                           .Cells(3, 1).Value2, _
                           .Cells(4, 1).Value2 _
                           ) & Format(countI, "-000")
        End With
      End If
    Next i
  
  End With
  
End Sub

 

[Joe4]

Trying turning on "Option Explicit" in your workbook, then try compiling.
See: Option Explicit in Excel VBA

It looks to me like you are trying to use a Worksheet variable named "Total1" that you never set.
If that is the name of your worksheet, you need to do this first:

Dim Total1 as Worksheet
Set Total1 = Sheets("Total1")

If you always use Option Explicit, like I mentioned above, it will catch these kind of errors (and typos).
Option Explicit forces you to declare any variables before using them.
And if you are using variables, you need to be sure to set them to something, as that does not happen automatically (it would have no idea what you want to set them equal to).

 

[saboh12617]

Hello, thanks for your quick reply.
I had specified Option Explicit indeed, and all my variables are set. Total1 is the worksheet's code name as shown below:

I also doubled checked the table name which is correct

 

[Joe4]

What exactly are you trying to do with that code?
It looks to me like you are populating an array, but then not doing anything with it.

 

[saboh12617]

Well yes to focus on the problem I took away the rest of the code which is fine as i already used it in another Sub.

I indeed populate an array and then i just take this array and paste it in my table

Essentially, i am using this sub to generate generic names for my files.

 

[Joe4]

Did you try inserting a break point at the very beginning of your code.
Then, when you call/run it, it will stop there, and you can use the F8 key to step through it one line at a time.
While you are doing that, you can hover over any variable to see what it is at that particular point in time.

I find this to be an especially helpful debugging technique when things don't seem to work as intended.
Many times, by seeing exactly what it is doing, and the values at each particular step, the issue becomes evident.

 

[saboh12617]

Okay never mind. My "Excel Instance" was corrupted. I restarted Excel and now the macro is working flawlessly.
I'm very sorry for the time lost and useless post. The code was not the problem.

 

[Joe4]

Thanks for letting us know.
Happy to hear that you figured out what the issue was.