Tag Archives: Visual Basic

VBA Macro: How to add and rename worksheets

In following demonstration video I will implement Excel Visual Basic macro that reads worksheet names from the cells A1, A2, A3, … in sheet “Sheet1” and creates new worksheets with correct names.

For simplicity there is no error handling etc and following simple code does the trick:

Sub createsheets()
    Dim newsheet As Worksheet
    Dim r As Integer
    r = 1
    Do While Sheets("Sheet1").Cells(r, 1).Value <> ""
        Set newsheet = Sheets.Add
        newsheet.Name = Sheets("Sheet1").Cells(r, 1).Value
        r = r + 1
    Loop
End Sub

Excel VBA Macro: How to name Worksheets

You can have several worksheets in one Excel workbook. Default names of worksheets are Sheet 1, Sheet 2, … You can change names manually by double clicking the name of the sheet, but you can also automatise this.

In the following demonstration video I will implement a simple VBA macro that names all worksheets in Excel workbook according to the content of the each worksheet’s cell A1. You can go through all worksheet objects by using For Each -loop:

Sub namesheets()
     Dim s As Worksheet
     For Each s In Worksheets
         s.Name = s.Cells(1, 1).Value
     Next
 End Sub

Excel VBA macro: How to implement running clock

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

  • RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE.
  • StartClock: Sets global variable clockOn = TRUE and calls RunClock
  • StopClock: Sets global variable clockOn = FALSE
Recursive call is made using Excel’s function
 
Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule)
In this case only first two parameters are needed:
  • EarliestTime: Time when Procedure is been run. In this case Now + TimeValue(“00:00:01”)
  • Procedure: The name of the procedure. In this case RunClock itself
If you want to create buttons for starting and stopping the clock, please check how to add button

Excel VBA macro: How to loop through selection

In following video I will show an Excel VBA macro example about how to loop through each selected cells and write location information to cells.

We can use Excel’s Selection object (Range type containing selected cells of the active worksheet) and cycle through selected cells by using For-Each-Next loop:

For Each c in Selection
    ...
    c.Value = ...
    ...
Next

c stands for single cell (Range type).

Following video clarifies this.

Excel VBA macro example: How to count concurrent times

Here is an example VBA macro for solving a call log analysis problem:

We have call log

  • Column A: Date of the call
  • Column B: Time of the call
  • Column C: Duration of the call

How many concurrent calls?

Following screen capture demonstrates the situation:

Screen Shot 2013-02-12 at 11.54.03

To solve the problem I implemented Visual Basic macro ConcurrentCalls() and helper functions max(), min() and isConcurrent(). Here is the code:

Function max(a, b)
  If a > b Then
    max = a
  Else
    max = b
  End If
End Function

Function min(a, b)
  If a > b Then
    min = b
  Else
    min = a
  End If
End Function

Function isConcurrent(start1, end1, start2, end2)
  If max(start1, start2) <= min(end1, end2) Then
    isConcurrent = True
  Els
    isConcurrent = False
  End If
End Function

Function ConcurrentCalls()
    maxconcurrent = 1
    r1 = 2
    Do
        concurrent = 1
        r2 = 2
        Do
            start1 = Cells(r1, 1).Value + Cells(r1, 2).Value
            end1 = start1 + Cells(r1, 3).Value
            start2 = Cells(r2, 1).Value + Cells(r2, 2).Value
            end2 = start2 + Cells(r2, 3).Value
            If r1 <> r2 And isConcurrent(start1, end1, start2, end2) Then
                concurrent = concurrent + 1
            End If
            r2 = r2 + 1
        Loop Until Cells(r2, 1) = ""
        maxconcurrent = max(maxconcurrent, concurrent)

    Loop Until Cells(r1, 1) <> ""
    ConcurrentCalls = maxconcurrent
End Function

Feel free to examine and let me know if you see a bug or can imagine a better solution!

Check also 101 Excel Secrets and Everyday Excel video course.

Excel VBA Macro: How to use InputBox

Following example video demonstrates how to use Input Box dialog from the Excel VBA Macro code.  In this example input type is Range-object. Example code asks user to select cells and then fills those cells with text “Foo”.

 

Excel VBA macros: How to record a VBA macro

It is a good idea to automatise time consuming routine tasks in Excel. You don’t need to know how to implement VBA macros, because you can just record your tasks. In following example I record a macro that assumes that I have people’s whole names listed in column A and want to split names (first name and last name) into columns A anB. This is good example when simple recorded macro can save you from doing this routine task all over again.

Here is a video that demonstrates the VBA macro recording and shows how to run the macro:

How to colour Excel chart according to source data using VBA

I will demonstrate you the power of Excel VBA macros with a nice simple macro that goes through all your chart objects and set the colours of those according to their source cells’ colour. If you are unfamiliar with Excel VBA, then I suggest that you first check my previous post Excel Tips and Tricks: How to write simple VBA function.

Here is the code that does the trick (I don’t guarantee that it works in every cases, but it should give you the idea):
Sub ColorCharts()
    For Each ch In ActiveSheet.ChartObjects
        For Each ser In ch.Chart.SeriesCollection
            s = Split(ser.Formula, ",")
            For i = 1 To UBound(ser.Values)
                ser.Points(i).Interior.Color = Range(s(2)).Cells(i).Interior.Color
            Next i
        Next ser
    Next ch
End Sub
Following video demonstrates this:

 

How to write Excel VBA function

If there is no proper function for your purposes, you can always write one by yourself. All you need is a basic understanding of Basic-programming. It is not hard!

In this demonstration we implement macro function for reversing text (e.g. “help”–>”pleh”). Following steps are needed:

  • Add Developer tab to Excel Ribbon bar:
    Excel 2011 for Mac: Select click gear in the Ribbon’s right edge and select Ribbon Preferences…
    Excel 2010 for Windows: Right clickon the Ribbon and select Customize Ribbon…

    –> Make sure that the Developer-Tab is selected in the Tab list
  • Select Developer-Tab
  • Start Excel’s Visual Basic -editor
  • Add new module by opening context menu from VBAProject and selecting Insert –> Module
  • Write following code to Module1
    Function TransposeText(txt)
        Dim returntxt
        returntxt = ""
        For i = Len(txt) To 1 Step -1
            returntxt = returntxt + Mid(txt, i, 1)
        Next i
        TransposeText = returntxt
    End Function
  • Now your function is ready to use
  • Go back to the worksheet and test the new function
Here is the video demonstrating this using Excel 2011 for Mac: