VBA Question

[Ddangelo]

I am trying to re-enter new information into this old program. I am reading the code to see where I would have to change ranges and such, and was wondering what the letters that are equal to a number are referring to?
(EX. i = 0, what is the "i" referring to? Thanks!

Function winRatioGen()
Dim cell As Range 'array for each row

'declear an array for each frame size. Can only fit 500 points. update as necessary
Dim Frame10(500) As Double
Dim Frame15(500) As Double
Dim Frame20(500) As Double
Dim Frame25(500) As Double
Dim Frame29(500) As Double
Dim Frame32(500) As Double
Dim Frame38(500) As Double
Dim Frame46(500) As Double
Dim Frame56(500) As Double
Dim Frame60(500) As Double
Dim Frame70(500) As Double
Dim Frame78(500) As Double
Dim Frame88(500) As Double
Dim Frame103(500) As Double
Dim Frame110(500) As Double

'counters for rows and each frame size.
i = 0
j = 0
k = 0
l = 0
m = 0
n = 0
o = 0
p = 0
q = 0
r = 0
s = 0
t = 0
u = 0
v = 0
w = 0
x = 0
y = 0
z = 0
a = 0
b = 0

'only take into account the data after autofilter.
Dim rng As Range
Dim cellRow As Integer

 

[pplstuff]

The letters are 'variables.' Variables are usually declared in the module (If the phrase "Option Explicit" is in the module, all variables MUST be declared). Experienced programmers tend to use Option Explict in their code, and it's generally seen as a 'best practice' to declare all variables. The "Dim" statement indicates declared variables, as you can see the author declared the variable "cell" as a range.

It looks like this is some pretty crappy code from the small snippet you've posted. But the creator was using these letters (a-z) to hold numbers. Generally, you'd see this line in code similar to this..
"Dim i as long"
Which says "I'm declaring the variable "i" as a whole number between -2,000,000,000 and +2,000,000,000."

Here's a list of variable types:
Excel VBA Variables. Using Variables in Excel VBA Macro Code

 

[Ddangelo]

Posted below is the entire function for this. Now are you saying that the frame sizes are variable declared?

Function winRatioGen()
Dim cell As Range 'array for each row

'declear an array for each frame size. Can only fit 500 points. update as necessary
Dim Frame10(500) As Double
Dim Frame15(500) As Double
Dim Frame20(500) As Double
Dim Frame25(500) As Double
Dim Frame29(500) As Double
Dim Frame32(500) As Double
Dim Frame38(500) As Double
Dim Frame46(500) As Double
Dim Frame56(500) As Double
Dim Frame60(500) As Double
Dim Frame70(500) As Double
Dim Frame78(500) As Double
Dim Frame88(500) As Double
Dim Frame103(500) As Double
Dim Frame110(500) As Double

'counters for rows and each frame size.
i = 0
j = 0
k = 0
l = 0
m = 0
n = 0
o = 0
p = 0
q = 0
r = 0
s = 0
t = 0
u = 0
v = 0
w = 0
x = 0
y = 0
z = 0
a = 0
b = 0

'only take into account the data after autofilter.
Dim rng As Range
Dim cellRow As Integer

Set rng = Worksheets("Baseline").Range("AT3:AT500").SpecialCells(xlCellTypeVisible)

'Assign values to each frame array
For Each cell In rng
cellRow = cell.Row
If cell = 10 Then
Frame10(j) = Worksheets("Baseline").Range("AU" & cellRow)
j = j + 1
ElseIf cell = 15 Then
Frame15(k) = Worksheets("Baseline").Range("AU" & cellRow)
k = k + 1
ElseIf cell = 20 Then
Frame20(l) = Worksheets("Baseline").Range("AU" & cellRow)
l = l + 1
ElseIf cell = 25 Then
Frame25(m) = Worksheets("Baseline").Range("AU" & cellRow)
m = m + 1
ElseIf cell = 29 Then
Frame29 = Worksheets("Baseline").Range("AU" & cellRow)
n = n + 1
ElseIf cell = 32 Then
Frame32(o) = Worksheets("Baseline").Range("AU" & cellRow)
o = o + 1
ElseIf cell = 38 Then
Frame38(p) = Worksheets("Baseline").Range("AU" & cellRow)
p = p + 1
ElseIf cell = 46 Then
Frame46(q) = Worksheets("Baseline").Range("AU" & cellRow)
q = q + 1
ElseIf cell = 56 Then
Frame56(r) = Worksheets("Baseline").Range("AU" & cellRow)
r = r + 1
ElseIf cell = 60 Then
Frame60(s) = Worksheets("Baseline").Range("AU" & cellRow)
s = s + 1
ElseIf cell = 70 Then
Frame70(t) = Worksheets("Baseline").Range("AU" & cellRow)
t = t + 1
ElseIf cell = 78 Then
Frame78(u) = Worksheets("Baseline").Range("AU" & cellRow)
u = u + 1
ElseIf cell = 88 Then
Frame88(v) = Worksheets("Baseline").Range("AU" & cellRow)
v = v + 1
ElseIf cell = 103 Then
Frame103(a) = Worksheets("Baseline").Range("AU" & cellRow)
a = a + 1
ElseIf cell = 110 Then
Frame110(b) = Worksheets("Baseline").Range("AU" & cellRow)
b = b + 1
End If

i = i + 1
Next cell

'input frame values into data table

Dim cell1 As Range
Dim cell2 As Range

Worksheets("WinRatio").Range("A15:N300").Clear
j = 0
For Each cell1 In Worksheets("WinRatio").Range("B14:N14") 'change range if more frame sizes are added
i = 0
For Each cell2 In Worksheets("WinRatio").Range("B15:B500")
If j = 0 Then
If Frame10(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame10(i)
End If
ElseIf j = 1 Then
If Frame15(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame15(i)
End If
ElseIf j = 2 Then
If Frame20(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame20(i)
End If
ElseIf j = 3 Then
If Frame25(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame25(i)
End If
ElseIf j = 4 Then
If Frame29(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame29(i)
End If
ElseIf j = 5 Then
If Frame32(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame32(i)
End If
ElseIf j = 6 Then
If Frame38(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame38(i)
End If
ElseIf j = 7 Then
If Frame46(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame46(i)
End If
ElseIf j = 8 Then
If Frame56(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame56(i)
End If
ElseIf j = 9 Then
If Frame60(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame60(i)
End If
ElseIf j = 10 Then
If Frame70(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame70(i)
End If
ElseIf j = 11 Then
If Frame78(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame78(i)
End If
ElseIf j = 12 Then
If Frame88(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame88(i)
End If
ElseIf j = 13 Then
If Frame103(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame103(i)
End If
ElseIf j = 14 Then
If Frame110(i) <> 0 Then
Worksheets("WinRatio").Cells(i + 15, j + 2) = Frame110(i)
End If
End If
i = i + 1
Next cell2

j = j + 1
Next cell1
Worksheets("WinRatio").Range("B15:N500").NumberFormat = "0.0000"

 

[jsotola]

can you post the whole macro?


spoke too soon, can't take back

 

[Ddangelo]

deleted the whole thing, since the function above is really the only place im having trouble
(this post I mean, I didn't delete all my code!)

 

[jsotola]

try this code

see if it works for you

Function winRatioGen()
    Dim cell As Range 'array for each row
    
    'declare an array for each frame size. Can only fit 500 points. update as necessary
    Dim frame(0 To 14, 1 To 500) As Double
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add 10, 0              ' translate 10,15,20 ... to  0,1,2,3,.....
    dict.Add 15, 1
    dict.Add 20, 2
    dict.Add 25, 3
    dict.Add 29, 4
    dict.Add 32, 5
    dict.Add 38, 6
    dict.Add 46, 7
    dict.Add 56, 8
    dict.Add 60, 9
    dict.Add 70, 10
    dict.Add 78, 11
    dict.Add 88, 12
    dict.Add 103, 13
    dict.Add 110, 14


'    Debug.Print dict.Exists(11)    ' test
'    Debug.Print dict.Item(11)
'    Debug.Print dict.Exists(103)
'    Debug.Print dict.Item(103)
    
    Dim indx(0 To 14) As Integer    ' 15 counters
    
    'only take into account the data after autofilter.
    Dim rng As Range
    Dim cellRow As Integer
    
    Set rng = Worksheets("Baseline").Range("AT3:AT500").SpecialCells(xlCellTypeVisible)
    
    'Assign values to each frame array
    For Each cell In rng
        cellRow = cell.Row
        
        If dict.Exists(cell) Then       ' check if value is one of 10, 15, 20 .... 103, 110
            j = dict(cell)              ' get associated index value  0, 1, 2 .... 14
            
            frame(j)(indx(j)) = Worksheets("Baseline").Range("AU" & cellRow)
            
            indx(j) = indx(j) + 1       ' increment row count
        End If
        
    Next cell
    
    'input frame values into data table
    
    Dim cell1 As Range
    Dim cell2 As Range
    
    Worksheets("WinRatio").Range("A15:N300").Clear
    j = 0
    For Each cell1 In Worksheets("WinRatio").Range("B14:N14") 'change range if more frame sizes are added
        i = 0
        For Each cell2 In Worksheets("WinRatio").Range("B15:B500")
            
            If frame(j)(i) <> 0 Then Worksheets("WinRatio").Cells(i + 15, j + 2) = frame(j)(i)
            i = i + 1
        
        Next cell2
        
        j = j + 1
    Next cell1
    
    Worksheets("WinRatio").Range("B15:N500").NumberFormat = "0.0000"


End Function