Archive for the ‘Workbook and Worksheet’ Category
I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook.
Below is a sample of how we can do this in Microsoft Excel *just an outline*.
The logic is simple, when we open the Workbook, we call for the first time the procedure needed to refresh the data. In that procedure, we also add a code to call back the procedure itself 15 minutes later. So while the Workbook is still open, the procedure will continue to call itself every 15 minutes.
' Put this procedure in ThisWorkbook module
Private Sub Workbook_Open()
' Run the procedure to refresh data when the workbook opened
Call RefreshData
End Sub
' Add new module and put this procedure in it
Public Sub RefreshData()
Debug.Print "Data refreshed at " & Now
'
' This is where we put everything needed to refresh the data
'
' Schedules this procedure to be run 15 minutes from now
Application.OnTime Now + TimeValue("00:15:00"), "RefreshData"
End Sub
Even looks like an infinite loop, this is not a problem for Excel when we use Application.OnTime function. The scheduling will stop automatically when the Workbook is close.
There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base.
Here a simple example of how to create a new set 12 Worksheets for each month of the year.
Sub CreateMonthlyWorksheetTemplate()
Dim ix As Integer, oSheet As Worksheet, sMonth As String
For ix = 1 To 12
'add new worksheet
Set oSheet = Worksheets.Add
'get month name from index
sMonth = Format("1/" & CStr(ix), "mmm")
'rename the new worksheet
oSheet.Name = sMonth
Set oSheet = Nothing
Next ix
End Sub
Read also a previous post about how vba add worksheet complete with error handler when we counter duplicate Worksheet.
Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date.
The algorithm is like this: I’ll create a loop from the first row until the last row with data and check if the date is before Jan 1st, 2009 or not, if it is, simply delete that row.
Sub DeleteFilteredRows()
Dim i As Long
For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(i, "A").Value
If CDate(Cells(i, "A")) < CDate("1/jan/2009") Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
Of course I’ll make an adjustment to the code above to meet my current condition.
FIN.
A few weeks ago I received this question from someone called LS:
Hi
I want to write a macro that counts the following
- Number of cells with formula
- Number of Rows with formula
- Number of Columns with formula
Please let me know if there is a way of doing it in Excel VBA
To find out the number of cells, rows, or columns with formula in active Excel worksheet is quite simple.
In my previous post, we already have the Excel VBA code on how to select all cells with formula, so we only need to tweak this macro a little bit and get the results that we looking for.
Sub NumberOfCellsWFormula()
'select all cells with formula
Cells.SpecialCells(xlCellTypeFormulas).Select
'number of cells with formula
Debug.Print Selection.Count
'number of rows with formula
Debug.Print Selection.Rows.Count
'number of columns with formula
Debug.Print Selection.Columns.Count
End Sub
The Excel VBA procedure above will simply print the number of cells, rows, and columns of current active Excel Worksheet that containing any formula in it. Please be sure to run it from Excel VBA windows with Immediate Window turn on (View » Immediate Window or CTRL+G).
In short, we only need to use Range properties Count, Rows.Count, and Columns.Count to get the number of selected cells, rows or columns.
FIN.
Auto run macro excel… Do you want to run an excel macros automatically when the file is open or close?
Microsoft Excel provide us the opportunity to run a macro automatically every time we open or close the excel workbook file, using the event Workbook_Open and Workbook_BeforeClose.
In the Microsoft Visual Basic Editor page, in the ThisWorkbook module, we simply create two new sub procedure called Workbook_Open and Workbook_BeforeClose. In both sub procedure, we call the method that we want to run automatically when the workbook is open or closed.
Private Sub Workbook_Open ()
Call onFileOpenMacro
End Sub
Private Sub Workbook_BeforeClose (Cancel As Boolean)
Call onFileCloseMacro
End Sub
In this example, when the workbook is opened, we will automatically run macros called onFileOpenMacro, we also automatically run macros called onFileCloseMacro just before the excel workbook is closed.
Converting Excel column number into column name, for example 1 into A or 52 into AZ, is needed occasionally, and the following function will do the convertion for us.
Function Number2Char(ByVal vNumber)
Dim iDiv As Double, iMod As Integer
If vNumber < 1 Then Exit Function
iDiv = vNumber
While iDiv > 26
iMod = iDiv Mod 26
If iMod = 0 Then
iMod = 26
iDiv = iDiv - 1
End If
Number2Char = Chr(64 + iMod) & Number2Char
iDiv = iDiv \ 26
Wend
Number2Char = Chr(64 + iDiv) & Number2Char
End Function
FIN.
Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells.
The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on the worksheet, and then using Excel FIND function we will find which cell contain that minimum/ maximum value.
Excel VBA macro implementation of the algorithm above will look like below, change code Application.Min(oRg) into Application.Max(oRg) to find the maximum value instead of minimum value.
Sub FindMinValue()
Dim oRg As Range, iMin As Variant
Set oRg = Cells
'Finding the minimum value
iMin = Application.Min(oRg)
'Select cell containing the min value
oRg.Find(What:=iMin, _
After:=oRg.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select
'Change selected cell format
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Displaying min value info
With Selection
MsgBox "Min value : " & iMin & vbCrLf & _
"Cell position " & vbCrLf & _
"Row : " & .Row & vbCrLf & _
"Column : " & .Column
End With
End Sub
As an additional info, the code above will display the cell info, row and column position of the cell containing the minimum/maximum value.
