Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: explain vba code

    Hi guy's

    i have the following working vba code below, it's listing the missing numbers in a sequence in a new column, but i don't now how the code is working

    i don't understand the for loops and the scripting.dictionary , is someone who can help me?

    Kind regards,

    Tom

    Code:
    Const rng As String = "$a$2:$a$218"
    Dim d As Object, a, c()
    Dim i As Long, mx As Long, mn As Long
    
    Set d = CreateObject("scripting.dictionary")
    a = Range(rng)
    
    mx = Application.Max(a): mn = Application.Min(a)
    
    ReDim c(1 To mx - mn + 1, 1 To 1)
    
    
    For i = 1 To UBound(a): d(a(i, 1)) = 1: Next i
    
    For i = mn To mx
        If d(i) <> 1 Then k = k + 1: c(k, 1) = i
    Next i

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by tj.waarsenburg View Post
    Code:
    Const rng As String = "$a$2:$a$218"
    Dim d As Object, a, c()
    Dim i As Long, mx As Long, mn As Long
    
    Set d = CreateObject("scripting.dictionary")
    
    ' Create a variable that references the data in cell range $a$2:$a$218
    a = Range(rng)
    
    Work out how big this range is (minimum and maximum boundaries)
    mx = Application.Max(a)
    mn = Application.Min(a)
    
    Now we know our min and max "reset" our array variable to the appropriate size
    ReDim c(1 To mx - mn + 1, 1 To 1)
    
    Loop over our new array
    For i = 1 To UBound(a)
        Umm?
        d(a(i, 1)) = 1
    Next i
    
    Then loop from min to max
    For i = mn To mx
        If d(i) <> 1 Then
            Increment k
            k = k + 1
        End If
        Umm?
        c(k, 1) = i
    Next i
    This is why you should always use proper variable names. How bloody confusing is that code!

    Note I've added in a few extra line breaks for readability. What is gained by leaving them out?

    Short code <> good code.

    Good code is easy to read, easy to maintain and commented where appropriate.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The FOR loop means it will start at the 1st #, assign it to I and continue incrementing until it get to the last # in the FOR statement. For i = 1 to 6.
    Every time it hits the NEXT statement, I gets incremented by 1.
    When the FOR value I gets to the last value, it falls out of the NEXT.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •