CODEWITHSUNDEEP
vbaexcel
arnau infante pinós
arnau infante pinós

Reputation: 35

Call sub in vba excel not working properly

I need to call a macro from another macro and I'm using the 'call' way. I don't understand why it doesn't work properly, unless you put a breakpoint in the call line.

See. I have this: 

Sub actualizarDatos()   
     Sheets("DATOS CITAS").Select
     Range("A1").Select
     Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

     ActiveWorkbook.RefreshAll

     Call replaceColumns

     Sheets("Resumen").Activate
End Sub

The first part is written like this because I had some problems with a large sheet. It made my excel stop.

The macro that I'm calling looks like this:

 Sub replaceColumns()    
   With ThisWorkbook.Sheets("RFS")
       .Columns("A").Replace _
          What:="2", Replacement:="2", _
          SearchOrder:=xlByColumns, MatchCase:=True
   End With
End Sub

Why do I need the second macro? Because a column with formula didn't update automatically, so this macro "modify" a cell which is been used in the formula.

But let's go with the important part and the main qüestion:

Why the call part is not working? -if I execute the first macro in RFS sheet(The sheet in the second macro) it works! But I have to update it from a button in another sheet, so it isn't the answer. - if I put a breakpoint and execute the first macro from in a random sheet, it works.

I've also tried with the macro name directly, not using 'Call'. Not working. And even writing the macro content directly inside the first macro, where the call line is written, it doesn't work!! For some reason it misses that line. Does anyone know why?

Thank you!

[EDIT]

SOLVED

I had to do something in the configuration of my tables. If someone have the same problem with the refreshing, just to the next:

For each table you will refresh in RefreshAll, select it and go to Design in the top menu. Now go to properties and uncheck the checkbox that says: Enable backgroud refresh. This is how it looks:

properties window to change the refresh settings of a table

Upvotes: 1

Views: 8407

Answers (1)

Alex Martinez
Alex Martinez

Reputation: 201

Did you try making the Sub Public. Like:

Public Sub replaceColumns()    
   With ThisWorkbook.Sheets("RFS")
       .Columns("A").Replace _
          What:="2", Replacement:="2", _
          SearchOrder:=xlByColumns, MatchCase:=True
   End With
End Sub

Also, for the first procedure, try to put a Sleep command to ensure you wait until Excel finishes refreshing. 

Sub actualizarDatos()   
     Sheets("DATOS CITAS").Select
     Range("A1").Select
     Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

     ActiveWorkbook.RefreshAll
     Sleep 2000
     Call replaceColumns

     Sheets("Resumen").Activate
End Sub

And put this code in the top of module:

#If VBA7 Then
  Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
   Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Upvotes: 1

Related Questions