Saturday, April 20, 2019

VBA Range Run-time error 91 Object variable or With block variable not set

Run-time error 91;Run-time error 91 Object variable or With block variable not set;Run-time error 91 while defining Range;VBA Range Run-time error 91 Object variable or With block variable not set;create 5 seconds before unload userform


Error:


I encountered the below error when I try to define a range variable.

Run-time error '91':
Object variable or With block variable not set


Code:

Dim changedRange As Range

changedRange = Sheets(sheetName).ListObjects(table).DataBodyRange --Error on this line

Solution:


Needed to use "Set", because I had defined changedRange as Range - and this is an Object ;)
Set changedRange = Sheets(sheetName).ListObjects(table).DataBodyRange

That solved the issue.


VBA - Create a time delay before unloading User Form

create delay to unload VBA userform;VBA;VBA unload userform delay;create 5 seconds before unload userform


I had a requirement to unload an VBA user form after few seconds delay, that I used to display progress of the tasks.

Solution:


You may try the below:

Application.Wait Now + TimeValue("0:00:03") 'Create a time delay of 3 seconds

Sample Code:

FrmProgress.Label1.Caption = "Loading data from database...."
FrmProgress.Show
FrmProgress.Repaint

'...code for whatever it is you want to do

Application.Wait Now + TimeValue("0:00:03") 'Create a time delay

Unload FrmProgress
Sample Code 2:

MsgBox "Running Script in Database...Please Wait..", vbInformation, "In-Progress"Wait (5)MsgBox "Script Executed Successfully!", vbInformation, "Execution Completed" Sub Wait(seconds As Integer)      Dim now As Long      now = Timer()      Do          DoEvents      Loop While (Timer < now + seconds)    End Sub