Early and Late Binding

f12

Good article on Early and Late Binding

MSDN Early and Late binding

Coding can appear very complicated sometimes but to simplify the difference in terms of implementation this line is very clear;

The only difference between early binding and late binding (in terms of the code you write) is in the variable declaration.

Or it could be an excuse to post a really nice picture of some bindings.

Scramble the values of individual cells in an Excel Spreadsheet

dice3

Just out of curiosity I went off and had a look for a function that does the same for excel spreadsheets as my previous post did for databases.

Here’s a function that will work.

Public Function Scramble(Optional ByRef UserText As Variant, Optional ByRef Everytime As Variant) As String

    On Error GoTo ScrambleError

   
    Dim i As Long
    Dim Num As Long
    Dim NewPosition As Long
    Dim Temp As String

   

    If IsMissing(UserText) Then
    Scramble = "No data"

     Exit Function
    ' No quotes automatically generates an error from the worksheet.

 

    ElseIf IsError(UserText) Then
    Scramble = "Error - try adding quote marks around your entry."
    Exit Function
    End If

 

    Application.Volatile (Not IsMissing(Everytime))
    If TypeName(UserText) = "Range" Then UserText = UserText(1).Value
    Num = Len(UserText)
    If Num > 0 Then
    For i = 1 To Num
    Temp = Mid$(UserText, i, 1)
    NewPosition = Int(Num * Rnd + 1)
    Mid$(UserText, i, 1) = Mid$(UserText, NewPosition, 1)
    Mid$(UserText, NewPosition, 1) = Temp
    Next ' i
    Scramble = UserText

    Else

    Scramble = "No data" 'Can result from entering ""
    End If
    Exit Function

ScrambleError:

    Scramble = "Error " & Err.Number

End Function