VBA Question

Ddangelo

New Member
Joined
Dec 17, 2013
Messages
16
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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(n) = 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"
 
Upvote 0
can you post the whole macro?


spoke too soon, can't take back
 
Last edited:
Upvote 0
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!)
 
Last edited:
Upvote 0
try this code

see if it works for you

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top