将Sub更改为Function然后在vba中调用 [英] Change Sub to Function then Call in vba

问题描述

我有一个密码生成器Sub，我想将其更改为Function，然后在宏中使用它来生成一个从B2开始的列，但每个单元格之后都是唯一的密码.

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.

它唯一要做的就是删除我的B1标头单元格.

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

谢谢

我有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

我的呼唤:

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

推荐答案

您需要将 strPassword 变量的值分配给函数 RandomPassword

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

同样在以下过程中，您将在 B列中获得最后使用的行，然后使用随机密码覆盖它们.我觉得您需要获取 A列的最后使用的行.

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

这篇关于将Sub更改为Function然后在vba中调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！