Password Generator formula

Question

I have the below code which works perfectly fine in a .xlsm file, however when I copy the code over to my Personal.xls file it no longer works. I have searched the forum and found similar threads, however none of them seem to hold the solution to my issue.

I'm sure you can see that my code is creating a password at random (20 characters log, but I have amended the formula to show only 3 characters for the purpose of this).

Sub Password_Generator()

Application.ScreenUpdating = False

Sheets.Add.Name = "Password"

Range("B1").Value = "A"
Range("B2").Value = "B"
Range("B3").Value = "C"
Range("B4").Value = "D"
Range("B5").Value = "E"
Range("B6").Value = "F"
Range("B7").Value = "G"
Range("B8").Value = "H"
Range("B9").Value = "I"
Range("B10").Value = "J"
Range("B11").Value = "K"
Range("B12").Value = "L"
Range("B13").Value = "M"
Range("B14").Value = "N"
Range("B15").Value = "O"
Range("B16").Value = "P"
Range("B17").Value = "Q"
Range("B18").Value = "R"
Range("B19").Value = "S"
Range("B20").Value = "T"
Range("B21").Value = "U"
Range("B22").Value = "V"
Range("B23").Value = "W"
Range("B24").Value = "X"
Range("B25").Value = "Y"
Range("B26").Value = "Z"
Range("B27").Value = "a"
Range("B28").Value = "b"
Range("B29").Value = "c"
Range("B30").Value = "d"
Range("B31").Value = "e"
Range("B32").Value = "f"
Range("B33").Value = "g"
Range("B34").Value = "h"
Range("B35").Value = "i"
Range("B36").Value = "j"
Range("B37").Value = "k"
Range("B38").Value = "l"
Range("B39").Value = "m"
Range("B40").Value = "n"
Range("B41").Value = "o"
Range("B42").Value = "p"
Range("B43").Value = "q"
Range("B44").Value = "r"
Range("B45").Value = "s"
Range("B46").Value = "t"
Range("B47").Value = "u"
Range("B48").Value = "v"
Range("B49").Value = "w"
Range("B50").Value = "x"
Range("B51").Value = "y"
Range("B52").Value = "z"
Range("B53").Value = "1"
Range("B54").Value = "2"
Range("B55").Value = "3"
Range("B56").Value = "4"
Range("B57").Value = "5"
Range("B58").Value = "6"
Range("B59").Value = "7"
Range("B60").Value = "8"
Range("B61").Value = "9"
Range("B62").Value = "0"
Range("B63").Value = "!"
Range("B64").Value = "£"
Range("B65").Value = "$"
Range("B66").Value = "%"
Range("B67").Value = "&"
Range("B68").Value = "*"

Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("A1:A3").Select
    Selection.AutoFill Destination:=Range("A1:A68"),       Type:=xlFillDefault
    Range("A1:A68").Select

Columns("A:B").Select
    Selection.EntireColumn.Hidden = True

Columns("G").Select
    Selection.ColumnWidth = 35

Range("G2").Value = "PASSWORD"
Range("G4").Value = "Select F9 to generate a new password"


Range("G3").Formula = "=VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)&VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)&VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)

Range("G2:G4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With

    Range("G2").Select
    Selection.Font.Bold = True
    Range("G4").Select
    Selection.Font.Italic = True
    Selection.Font.Size = 9
    Range("G3").Copy

Application.ScreenUpdating = False

End Sub

For me it works fine even in my Personal.xlsb. What does _it no longer works._ mean? Where do you get an error message saying _Wrong number of arguments or invalid property assignment_? — Storax, Oct 16 '18 at 09:34
Hi Storax - Thank you for taking the time to help me. When I run the code in my xlsm file it works, however when I create a new module in my Personal.xls file, copy the code in and run I get the error message, as per the title of my question. — Jamestm_PRE, Oct 16 '18 at 09:46
The code doesn't error at a particular line, the error message appears as soon as I attempt to run the code. — Jamestm_PRE, Oct 16 '18 at 09:51
Sorry but then it is difficult to help. You should get a dialog box where you can choose to debug which will bring you to the line we are looking for. — Storax, Oct 16 '18 at 09:53
Note that the code in your question cannot compile because in line `Range("G3").Formula …` a quote is missing. Probably this is a typo in the question only but make sure you check it. — Pᴇʜ, Oct 16 '18 at 09:56
Hmm, I copied the code and the quote was added by VBE automatically at least at my computer. I cannot really remove it because as soon as I leave the line the quote will be added again. @PEH:But, right, the quote is missing in the posting. — Storax, Oct 16 '18 at 10:00
Another question for the OP: Did you really put the code into a module of Personal.xls — Storax, Oct 16 '18 at 10:05
Then you have to [debug](https://www.excel-easy.com/vba/examples/debugging.html) and find the line with the error or follow PEH's advice to improve your code. — Storax, Oct 16 '18 at 10:13

Pᴇʜ · Accepted Answer · 2018-10-16T10:18:00.890

You must specify in which workbook you want to add a worksheet and specify a workbook for all ranges

Dim wsNew As Worksheet
Set wsNew = ActiveWorkbook.Worksheets.Add 
wsNew.Name = "Password"

you can access a range on that worksheet by

wsNew.Range("A1").FormulaR1C1 = "1"

Note that you should avoid using .Select because it is a bad practice (see How to avoid using Select in Excel VBA).

Apply this technique (specify worksheet and remove select) to every Range, Cells, Columns, Rows, etc. object.

Also note that you can reduce all these Range("B1").Value = "A" statements by a much shorter loop:

Const Letters As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!£$%&*"

Dim iLetter As Long
For iLetter = 1 To Len(Letters)
    wsNew.Cells(iLetter, "B").Value = Mid$(Letters, iLetter, 1)
Next iLetter

You would end up with something like that, which should work:

Sub Password_Generator()
    Application.ScreenUpdating = False

    Dim wsNew As Worksheet

    On Error GoTo ERR_ADDSHEET 'catch error if worksheet exists
    Set wsNew = Worksheets.Add
    On Error GoTo 0 're-enable error reporting

    wsNew.Name = "Password"

    Const Letters As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!£$%&*"

    Dim iLetter As Long
    For iLetter = 1 To Len(Letters)
        wsNew.Cells(iLetter, "B").Value = Mid$(Letters, iLetter, 1)
    Next iLetter

    With wsNew
        .Range("A1").Value = "1"
        .Range("A2").Value = "2"
        .Range("A3").Value = "3"
        .Range("A1:A3").AutoFill Destination:=.Range("A1:A" & Len(Letters)), Type:=xlFillDefault

        .Columns("A:B").EntireColumn.Hidden = True
        .Columns("G").ColumnWidth = 35

        .Range("G2").Value = "PASSWORD"
        .Range("G4").Value = "Select F9 to generate a new password"

        .Range("G3").Formula = "=VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)&VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)&VLOOKUP(RANDBETWEEN(1,68),A:B,2,0)"

        .Range("G2:G4").HorizontalAlignment = xlCenter
        .Range("G2:G4").VerticalAlignment = xlCenter

        .Range("G2").Font.Bold = True
        .Range("G4").Font.Italic = True
        .Range("G4").Font.Size = 9
        .Range("G3").Copy
    End With

ERR_ADDSHEET:
    Application.ScreenUpdating = True

    If Err.Number > 0 Then MsgBox "Worksheet could not be added.", vbCritical
End Sub

Hi, This has worked in my Personal file. Thank you so much for the help. — Jamestm_PRE, Oct 16 '18 at 10:30

Password Generator formula

1 Answers1