New to VBA

DeductiveBear23 · #1 04-07-2017, 09:55 AM

Hello All,

Very new to the entire world of VBA and Macros. I started with a want to add a random password generator to Word document. Some other people wanted the same thing and they were provided with some code that works exactly how I would like to it to.
This being said, I would like the randomized string of characters to appear in a table titled 'Password' instead of the message box. The code I am using is:

Code:

Sub PASSWORD_GENERATOR()
MsgBox Pwd(8)
End Sub
 
Function Pwd(iLength As Integer) As String
Dim i As Integer, iTemp As Integer, bOK As Boolean, strTemp As String
'48-57 = 0 To 9, 65-90 = A To Z, 97-122 = a To z
'amend For other characters If required
For i = 1 To iLength
  Do
    iTemp = Int((122 - 48 + 1) * Rnd + 48)
    Select Case iTemp
      Case 48 To 57, 65 To 90, 97 To 122: bOK = True
      Case Else: bOK = False
    End Select
  Loop Until bOK = True
  bOK = False
  strTemp = strTemp & Chr(iTemp)
Next i
Pwd = strTemp
End Function

I was able to produce the desired result in an Excel cell by changing the second last line where 'Pwd = strTemp' to ActiveSheet.Range("C2").Formula = pw (C2 being any cell I need it to be). I have tried "ActivTable.Range ("Password") Formula = pw" but I always get a 424 error and am not sure what or where to place the missing 'Object'.

As always - Any and all assistance is appreciated - Thank you

dwirony · #2 04-07-2017, 02:46 PM

Instead of "MsgBox Pwd(8), try:

Code:

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
        1, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    Selection.TypeText Text:="Password"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=Pwd(8)

DeductiveBear23 · #3 04-11-2017, 05:02 AM

Thank you DW. I will try to make time later today and try your code and will post back to let you know how I make out.

Regards,

DeductiveBear23 · #4 04-13-2017, 08:44 AM

Hi DW,

I have finally found a minute to try the code you suggested and I have developed a compile error. "Invalid outside Procedure". Do you have any suggestions?

dwirony · #5 04-14-2017, 10:06 AM

Strange, worked just fine for me. Are you using the whole new block of code? It still needs the function:

Code:

Sub PASSWORD_GENERATOR()
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
        1, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    Selection.TypeText Text:="Password"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=Pwd(8)
End Sub

Function Pwd(iLength As Integer) As String
Dim i As Integer, iTemp As Integer, bOK As Boolean, strTemp As String
'48-57 = 0 To 9, 65-90 = A To Z, 97-122 = a To z
'amend For other characters If required
For i = 1 To iLength
Do
iTemp = Int((122 - 48 + 1) * Rnd + 48)
Select Case iTemp
Case 48 To 57, 65 To 90, 97 To 122: bOK = True
Case Else: bOK = False
End Select
Loop Until bOK = True
bOK = False
strTemp = strTemp & Chr(iTemp)
Next i
Pwd = strTemp
End Function

DeductiveBear23 · #6 05-02-2017, 08:01 AM

Hi DW,

Sorry for the very late reply. I have been nursing a cold and trying to deploy a bunch of machines. I have finally found time to re-visit this issue and the code you provided works great! Thank you very much.

dwirony · #7 05-02-2017, 08:09 AM

No problem, DeductiveBear23. Feel better soon-

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode