How to implement sleep/wait with VBA

Excel-Sleep

Ok, everybody need this. Every single programmer that I’ve known once in his miserable life needed to do this in your programs. Alright, this is too dramatic, but it’s true. The problem is, in VBA, there is no easy going way to find a “how to do this”, like it is in other languages. But don’t worry. I’m here to save your lives, or something like that. Ok, too dramatic again.

With VBA, there are a couple of ways to do this.

First Method: Use an empty loop For… Next

The problem here is that you don’t have any control on how long it will take/sleep/wait/whatever. It’s just a way to make your program do nothing for a while. The code below runs for a while, given to the user the impression of be waiting.

1
2
3
4
Sub MyDelayMacro
 For iCount = 1 to 1000
 Next iCount
End Sub

Second Method: Use an API to call the Sleep method

Yes, there a Sleep method which can be called from VBA! This causes the same effect as the other programs, holding the process of the program for a defined amount of time, in milliseconds.

The kernel32 lib contains this method, so, you need to declare it in the top module to call it.

1
2
   Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
 (ByVal dwMilliseconds As Long)

To call the Sleep method::

1
2
3
Sub Sleep()
 Sleep 1000   'Faz o código esperar por 1 segundo
 End Sub

Third Method: Use the OnTime Method

Actually, it’s my favourite. The OnTime method is embedded in VBA and the programmers are often used to it. The syntax is a bit weird, but it works!

expression .OnTime (when, name, fail tolerance)

It requires the name of the macro/method which will be called after some time, which is the first argument of the method. So, as it seems to be, you will need two macros. The first one will activate the OnTime, while the second will be called by the OnTime.

In the code below, the “MyMainMacro” activate the OnTime to call “MyDelayMacro” after 15 seconds.

1
2
3
4
5
6
7
8
Sub MyMainMacro()
 ' Pausa por 15 segundos.
 Application.OnTime Now + TimeValue("00:00:15"), "MyDelayMacro"
 End Sub
Public Sub MyDelayMacro()
 ' Macro executada sob o agendamento.
 MsgBox "Esta macro foi executada após 15 segundos."
 End Sub

Fourth Method: Call the Application.Wait method

It’s similar to Sleep, but instead of send the amount of seconds as an argument, you need to build a TimeValue variable to send as a parameter. You need to think a bit different to use, because the program will wait to the time you have defined. Let’s see take a look at the code:

1
2
3
4
5
6
7
Sub MyWaitMacro()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub

To read more about the OnTime method, check this post: https://www.tomasvasquez.com.br/blog/microsoft-office/vba-agendando-a-execucao-de-macros-com-a-funcao-ontime

Enjoy!

Cuidado com Userform.Hide!

Vejo muitos exemplos de código em VBA que no uso de UserForms, usam o método Hide fechá-lo.

É bom tomar cuidado com o método Hide. Ele não fecha o Form, apenas esconde como o próprio nome diz.

O Unload definitivamente o tira da memória. Para fazer um teste, crie um form vazio, coloque nele um textbox e dois botões. Num deles coloque o Unload Me e no outro o UserForm.Hide.

Crie uma macro para chamar o Form com Userform.Show. Execute esta macro, coloque algum texto no textbox e clique no botão que executa o Hide. Execute a macro para chamar o Form de novo. Veja que o texto digitado continua lá. Agora faça o mesmo teste, mas agora clicando no botão que executa o Unload Me. Veja que a caixa de texto volta vazia.

O que o Hide faz é somente ocultar o Form. Se essa não for a intenção, é bom tomar cuidado com seu uso.

Abraços

Tomás Vásquez
https://www.tomasvasquez.com.br

Disabling close button in UserForm VBA

Alright, you’ve built an amazing Userform for your user, which works perfectly on every single function. A master piece of software! But, something remains out of your control, a simple click and everything is lost due to… the infamous close button! Ok, you know there is an easy and clever way to avoid this situation, handling the Query_Close event and the Cancel parameter.

So far so good, but… why do you and your user need this button there? Until this question remains unsolved, let’s look to another way to set our lives free about this problem.

Uma necessidade comum em alguns sistemas VBA é evitar que o usuário feche o form involuntariamente. A forma mais comum é captura o evento Query_Close e alterar o valor do parâmetro Cancel.

Porém algumas vezes é necessário que o botão de fechamento nem apareça para alterar. Para isso, basta colocar o código abaixo em um UserForm VBA. Os eventos Initialize e Query_Close já criados tratam de remover o botão do UserForm.

Private Declare Function FindWindowA Lib "USER32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLongA Lib "USER32" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLongA Lib "USER32" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
 
Private Sub UserForm_Initialize()
    Dim hwnd As Long
    hwnd = FindWindowA(vbNullString, Me.Caption)
    SetWindowLongA hwnd, -16, GetWindowLongA(hwnd, -16) And &HFFF7FFFF
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim hwnd As Long
    hwnd = FindWindowA(vbNullString, Me.Caption)
    SetWindowLongA hwnd, -16, GetWindowLongA(hwnd, -16) Or &H80000
End Sub

É bom atentar que desaparecer com o botão não desativa o uso do atalho Alt+F4 que fecha a janela.

Bom proveito!

Tomás Vásquez
https://www.tomasvasquez.com.br

Obtendo informações de um drive no VBA

Colaboração do camarada Robert Martin (http://br.msofficegurus.com/), postou uma dica muito bacana sobre como obter informações de um drive usando o VBA. A função abaixo por exemplo retorna o número de série de um drive baseado em sua letra de referência:

Function SerialPD(ByVal DriveLetter As String) As String
    Dim oFso As New FileSystemObject
    Dim oDrive As Drive
 
    If oFso.DriveExists(DriveLetter) Then
        Set oDrive = oFso.GetDrive(DriveLetter)
        SerialPD = Left(Hex(oDrive.SerialNumber), 4) & "-" & Right(oDrive.SerialNumber, 4)
    Else
        SerialPD = "Drive especificado nao existe."
    End If
 
    Set oDrive = Nothing
    Set oFso = Nothing
 
End Function

É necessária a referência à biblioteca Microsoft Scripting Runtime. No código, o objeto oDrive representa o Drive, que além da propriedade SerialNumber utilizada no código, possui outras como espaço livre, capacidade e sistema de arquivos. Uma documentação completa da biblioteca pode ser vista no site MSDN:

http://msdn.microsoft.com/en-us/library/bkx696eh%28VS.85%29.aspx

Bom proveito e valeu Robert!

Tomás Vásquez
https://www.tomasvasquez.com.br