Blog of Calc LLC

This example tells you how to calculate sum of values using VBA. Of course, you can achieve this by using the SUM() function without any VBA. This example demonstrates how to achieve it using VBA. Suppose you have a sales data for four regions, and you want to know the total of sales. See the data below. The VBA code is this: Sub sum_sales()     Dim row As Integer     Dim sum As Single     sum = 0     Dim sale As Single     For row = 2 To 5         sale = Cells(row, 2) 'grab cell values         sum = sum + sale     Next     Cells(6, 2) = sum 'put the calculated result in the right cell     MsgBox ("done") End Sub By running the code, you get the sum:

Suppose you have a sales data for four regions, and you want to know the region with the highest sales. See the data below. The VBA code is this: Sub find_max()     Dim row As Integer     Dim max As Single     max = 0     Dim sale As Single     For row = 2 To 5         sale = Cells(row, 2)         If sale > max Then             max = sale         End If     Next     Cells(6, 2) = max     MsgBox ("done") End Sub By running the code, you get the maximum value: If you want to find the minimum value instead of the maximum value, the code is very similar. Sub find_min()     Dim row As Integer     Dim min As Single     min = 10000 'select a very large value     Dim sale As Single     For row = 2 To 5         sale = Cells(row, 2)       ...

Suppose you have a table of students scores for the math exam. Also assume the maximum possible score is 100. Therefore, any value above 100 is an error. And you want to highlight the error values in red. See the data below. The VBA code is this: 'If a score>100, it is an error. Mark it in red. Sub Change_color()     Dim row As Integer     Dim score As Integer     For row = 2 To 16         score = Cells(row, 2)         If score > 100 Then             Cells(row, 2).Font.Color = RGB(255, 0, 0)         End If     Next     MsgBox ("done") End Sub The RGB() function is the function that Excel uses to set colors. RGB(255, 0, 0) is for red. By running the code, you get the maximum value:

If you are conducting a randomized clinical trial, you need to assign patients randomly into the "treatment group" and "control group". It can be achieved by the following code. 'generate random numbers, and put 'patients in different groups based 'on the random number Sub RandomNumber()     Dim row As Integer     Dim r As Single     For row = 2 To 17         r = Rnd()         Cells(row, 2) = r         Dim group As String         If r < 0.5 Then             group = "Treatment group"         Else             group = "Control group"         End If         Cells(row, 3) = group     Next     MsgBox ("done") End Sub The result is below.

Suppose you want to make a table of sales of your company in each regions in Quarter 1 and 2, like below. This code will achieve it. Sub Make_table()     Sheets("Sheet1").Cells(1, 1) = "Region"     Sheets("Sheet1").Cells(2, 1) = "East"     Sheets("Sheet1").Cells(3, 1) = "West"     Sheets("Sheet1").Cells(4, 1) = "South"     Sheets("Sheet1").Cells(5, 1) = "North"     Sheets("Sheet1").Cells(1, 2) = "Quarter 1"     Sheets("Sheet1").Cells(1, 3) = "Quarter 2"     Sheets("Sheet1").Cells(2, 2) = 52     Sheets("Sheet1").Cells(3, 2) = 47     Sheets("Sheet1").Cells(4, 2) = 68     Sheets("Sheet1").Cells(5, 3) = 75     Sheets("Sheet1").Cells(2, 3) = 89     Sheets("Sheet1").Cells(3, 3) = 65     Sheets("Sheet1").Cells(4, 3) = 79     Sheets("Sheet1").Cells(5, 3) = 100     MsgBox (...