Call sub in vba excel not working properly

Question

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:

"I'm using the 'call' way" -- why? `Call replaceColumns` is equivalent to `replaceColumns`. The keyword `Call` is legacy cruft. Also, replacing `"2"` by `"2"` is unmotivated to say the least. If you want to trigger a calculation, just use `Application.Calculate`. — John Coleman, Feb 14 '18 at 12:25
Replace `ActiveWorkbook` with `ThisWorkbook` and avoid using `.Select` and `Selection.` (there are many tutorials on Google how to avoid them). — Pᴇʜ, Feb 14 '18 at 12:27
See this for more on `Call`: https://stackoverflow.com/q/479891/4996248 — John Coleman, Feb 14 '18 at 12:30
without the call part, still not working. I've already tried. — arnau infante pinós, Feb 14 '18 at 12:33
I've tried almost everything. That's how I discovered that with a breakpoint, yeah it stops in that line and executes it, but without it, it just skip the method called — arnau infante pinós, Feb 14 '18 at 12:36
@JohnColeman The replacing is a bad "solution", but the formula are inside a table, so it calculates automatically. I discovered that if I want to callculate, I had to enter into the cell where I get a number and the go to another cell, and it calculate. So I made that line to replace a number that will be ALWAYS in the cell to calculate the formula — arnau infante pinós, Feb 14 '18 at 12:44
Replacing 2 with 2 in one column may be so fast that you may not notice it. Insert a `debug.print something` in the replaceColumns and you can see if the macro runs. — AcsErno, Feb 14 '18 at 12:44
Why not just use `Application.Calculate`? This is equivalent to pressing `F9` on the user interface. — John Coleman, Feb 14 '18 at 12:45
Application.Calculate is not working in this case, I tried it before doing that lines. until I don't "change" the data in the row that I'm taking for the formula, the formula doesn't calculate — arnau infante pinós, Feb 14 '18 at 12:51
What do you mean Application.Calculate is "not working"? How do you know? And how do you know the "Call replaceColumns" is not working? Add a line like "MsgBox 'In replaceColumns'" to the replaceColumns sub to see if it gets called or not. I bet you it does get called, you only think it doesn't. — Excel Developers, Feb 14 '18 at 13:02
*What* isn't calculating? If a dependent cell changes (or any dependent cell is volatile) then `Application.Calculate` should work. Is it a VBA function used as a UDF which isn't calculating? In that case, add `Application.Volatile` to the definition of the function. Without seeing a [mcve] it is hard to do anything more than guess. — John Coleman, Feb 14 '18 at 13:05
Sorry for the confusion with the application.calculate is not working. It IS working, BUT in my case is not the solution. I'm gonna try to explain it in a few comments. First I should explain you the structure of excel to make you understand what is happening. 2 sheets: sheet1: I have 3 columns, id and status(only ok or ko),and another column(there are the formula). sheet2: all the id that are ok. In the sheet1, I get data from a sql database as a table. — arnau infante pinós, Feb 14 '18 at 15:08
The formula is(in the first row): = IF.ERROR(VLOOKUP(A2;tabConfluence;2;FALSE);"KO") Which means that I look for my id in the other sheet, and it returns me the ok(if the id is in the second sheet) OR put KO in case it is not. In ALL the rows, I get KO, because the excel can't look for the id. What is the solution I found? replace a number(it always starts with the same number) for the same number, and ta daah! it works! Ok but now, I need to do this in a macro. When I call the macro, it does not change. That's why I have a problem. — arnau infante pinós, Feb 14 '18 at 15:14
Is possible that excel doesn't wait until the refresh is done and it execute the replaceColumns before the sheet RFS has been refreshed? I forgot to explain you that I have more sheets to refresh, that's why I suppose it could be the problem — arnau infante pinós, Feb 14 '18 at 15:17

Alex Martinez · Accepted Answer · 2018-02-14T15:48:18.253

1

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

edited Feb 14 '18 at 15:48

answered Feb 14 '18 at 13:46

Alex Martinez

201
1
9

1

This seems more like a comment than an answer. If this was the issue, the attempt to call it from outside its scope would raise an error, which is *not* the problem that OP describes. – John Coleman Feb 14 '18 at 13:50
It doesn't let me comment in the block above because I don't have 50 oints yet, sorry. – Alex Martinez Feb 14 '18 at 13:51
I'm not trying to get 50 point, I thought my suggestion could help and I dind't have any other option. Sorry. – Alex Martinez Feb 14 '18 at 14:00
Thank you, That's what I thought coming back home. May be the problem is that excel is not waiting till the refresh finishes to do the replaceColumns, and it is doing it before the RFS sheet refreshes, so I lose the replacement. Still don't know how is possible that if I put a breakpoint it works perfect, and does the replaceColumns at the end – arnau infante pinós Feb 14 '18 at 15:21
Did you try putting a "Sleep 2000" after RefreshAll? I updated my response! – Alex Martinez Feb 14 '18 at 15:43
Not yet, tomorrow morning I’ll try. I’m at home right now, but I’m pretty sure that it isn’t waiting till refreshing ends. Why? Because I put a message box inside the replaceColumns macro, and it shows before the refreshing ends, so the replaceColumns is working but it dissapears when refreshing RFS happens – arnau infante pinós Feb 14 '18 at 16:02
1

You have added enough to your original answer so that it is now much more than a comment. +1 – John Coleman Feb 14 '18 at 16:04
1

Thanks JohnColeman :) I have 50+points now. Won't Answer instead of commenting anymore. @arnauinfantepinós Let us know. This is probably why with the breakpoint works, by stopping there you are giving Excel chance to refresh. – Alex Martinez Feb 14 '18 at 16:15
@AlexMartinez I hope that you now answer in addition to (rather than instead of) commenting. – John Coleman Feb 14 '18 at 16:17
@AlexMartinez Still not working. The Sleep 2000 stops the whole macro, even the refreshing. I've tried with more time(20 seconds) but still not working. It's crazy how it works, it stops the refreshing to continue with the next line and then keeps refreshing – arnau infante pinós Feb 15 '18 at 08:07
Got it! I had to disable the checkbox in all my querytables properties that says: Enable background refresh. Now it refresh and then continue with the next line. Thank you so much for the help – arnau infante pinós Feb 15 '18 at 08:19

Call sub in vba excel not working properly

1 Answers1