CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Change Sub to Function then Call in vba

I have a password generator Sub, I want to change it to a Function and the use it in a Macro to generate a column starting on B2 were each cell after is a unique password.

The only thing it does is delete my B1 Header cell.

Thanks

I have the Sub:

Sub RandomPassword()
 Dim i As Integer

  For i = 1 To 8
    If i Mod 2 = 0 Then
        strPassword = Chr(Int((122 - 48 + 1) * Rnd + 48)) & strPassword
    Else
        strPassword = Int((9 * Rnd) + 1) & strPassword
    End If
 Next i
 MsgBox strPassword
End Sub

My Atempt and turning it in to a Function:

Function RandomPassword(strPassword As String) As String
 Dim i As Integer

 For i = 1 To 8
    If i Mod 2 = 0 Then
        strPassword = Chr(Int((122 - 48 + 1) * Rnd + 48)) & strPassword
    Else
        strPassword = Int((9 * Rnd) + 1) & strPassword
    End If
 Next i
End Function

My Calling it:

Sub qqq()

    Dim rng As range
    Dim lastRow As Long

    With Sheets("sheet1")
        lastRow = .range("B" & .Rows.Count).End(xlUp).Row
    End With

    For Each rng In Sheets("Sheet1").range("B2:B" & lastRow)
        rng.Value = RandomPassword(rng.Value)
    Next
End Sub

Upvotes: 0

Views: 3486

Answers (2)

Santosh
Santosh

Reputation: 12353

You need to assign the value of strPassword variable to function RandomPassword

Function RandomPassword(ByVal strPassword As String) As String
    Dim i As Integer

    For i = 1 To 8
        If i Mod 2 = 0 Then
            strPassword = Chr(Int((122 - 48 + 1) * Rnd + 48)) & strPassword
        Else
            strPassword = Int((9 * Rnd) + 1) & strPassword
        End If
    Next i

    RandomPassword = strPassword
End Function


Also in below procedure you are getting the last used row in column B and then overwriting them with random password. I feel like it you need to get the last used row of column A instead. 

Sub qqq()

    Dim rng As range
    Dim lastRow As Long

    With Sheets("sheet1")
        lastRow = .range("A" & .Rows.Count).End(xlUp).Row
    End With

    For Each rng In Sheets("Sheet1").range("B2:B" & lastRow)
        rng.Value = RandomPassword(rng.Value)
    Next
End Sub

Upvotes: 1

Jüri Ruut
Jüri Ruut

Reputation: 2530

You need an extra row in your function, just before End Function: 

RandomPassword=strPassword

Otherwise your function will have no value, resulting in empty cell.

Upvotes: 1

Related Questions