How to apply a formula on each row of table in Excel VBA

Table-valued functions in finaquant .net libraries

How to apply a formula on each row of table in Excel VBA

Postby tunc » 02 Sep 2014, 20:28

Hello all

Following example shows how a conditional formula can be applied on each row of a table in excel VBA (macro). This macro resembles the Row Transformer in finaquant's table function libraries (Protos or Calcs).

Image

In this example, we have students from different classes and their notes from various lectures listed in an table (ListObject). The task is calculating weighted notes by applying given formulas depending on the corresponding Method (A, B, C) and Class.

In other words, there is an overall formula which also depends on the attributes Method and Class.

See below, how this could be implemented in excel VBA:

Tunc

NOTE: In order to define an excel table (ListObject) select the range of data including column names, then press Table in Insert tab. A name like Table1 will be assigned to the ListObject automatically. You can change this name by overwriting the Table Name in Design tab.

In the example below, the ListObject's named "Notes" is in the worksheet named "Students".

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. ' Calculate weighted notes of students
  2. ' Worksheet: Students, ListObject (excel table): Notes
  3. Sub CalculateWeightedNotes()
  4. Dim tbl As ListObject
  5. Dim ws As Worksheet
  6. Dim Method, Student As String
  7. Dim Class As Integer
  8. Dim Mathe, Literature, Sports, Music, WNote As Double
  9. Dim r As Range
  10.  
  11. ' get worksheet
  12. Set ws = ThisWorkbook.Worksheets("Students")
  13.  
  14. ' get excel table
  15. Set tbl = ws.ListObjects("Notes")
  16.  
  17. Debug.Print tbl.ListRows.Count
  18.  
  19. For i = 1 To tbl.ListRows.Count     ' first row is header
  20.  
  21.     ' Get row
  22.    Set row = tbl.ListRows(i)
  23.  
  24.     ' Get all parameters
  25.    Student = tbl.ListColumns("student").Range(i + 1, 1)
  26.     Class = tbl.ListColumns("class").Range(i + 1, 1)
  27.     Method = tbl.ListColumns("method").Range(i + 1, 1)
  28.     Mathe = tbl.ListColumns("math").Range(i + 1, 1)
  29.     Literature = tbl.ListColumns("literature").Range(i + 1, 1)
  30.     Sports = tbl.ListColumns("sports").Range(i + 1, 1)
  31.     Music = tbl.ListColumns("music").Range(i + 1, 1)
  32.    
  33.     Debug.Print Method ' test
  34.    
  35.     ' call selected formula
  36.    Select Case Method
  37.         Case "A"
  38.        
  39.             If Class = 1 Then
  40.                 WNote = (0.8 * Mathe + 0.8 * Literature + 1.2 * Sports + 1.2 * Music) / 4
  41.             Else
  42.                 WNote = (Mathe + Literature + Sports + Music) / 4
  43.             End If
  44.        
  45.         Case "B"
  46.             WNote = (1.2 * Mathe + 1.2 * Literature + 0.8 * Sports + 0.8 * Music) / 4
  47.        
  48.         Case "C"
  49.             WNote = (Mathe + Literature + Sports + Music + 10) / 5
  50.        
  51.         Case Else
  52.             MsgBox "Unknown Method!"
  53.             Exit Sub
  54.     End Select
  55.    
  56.     ' assign weighted note to cell
  57.    Set r = tbl.ListColumns("weighted_note").Range(i + 1, 1)
  58.     r(1, 1) = WNote
  59. Next i
  60.  
  61. End Sub
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Return to Table Functions

Who is online

Users browsing this forum: No registered users and 2 guests

cron