Lookingt for some suggestions

[Mekinnik]

After a couple of weeks, it time to get back into this project. To refresh, I have created a password generator program, which generates a random password 15 to 32 characters long. Recently another rule was added to what the passwords can not include. An excel spreadsheet was given to me with roughly 1100 rows of data in a single colunm. Each cell contains an acronym; the rule is that the generated password can not contain any of the acronyms listed(whether upper or lowercase or a combination of the two). I am not sure how to do this, so I tought I would ask for a little help in determining what would be the best course of action to acomplish this task. I am open to any suggestions, in the past I have gotten tremendous help from a lot of people in this forum. I was thinking of some type of InStr function, but am not quite sure.

Thanks to all for helping

[dglienna]

Ditch the whole mess, and do what I suggested in your first thread.

Use WINDOW AUTHENTICATION, and authenticate the user's pw with the one stored on the system.

On the other hand, this sounds like a homework assignment anyways.

[Mekinnik]

Let me tell you, I wish it was. However it is not, I have to create this for work, we are not allowed to use WINDOW AUTHENTICATION, so I am trying to create it myself.

Thanks anyway

[dglienna]

Well, load the spreadsheet into a DB file, and you can search easier, I'd say.

[DataMiser]

One way would be that once your password has passed the other tests. Loop through the 1100 items in the file, check each using

If Instr(1,Password,Datafield,VBTextCompare)>0 then

[Mekinnik]

DataMiser, thanks for the suggestion, I have tried to use it, but it still needs some work. Is there a way to make the InStr function not case sensitive. The way it is now it does not see the "faQ" because in the spreadsheet everything is listed in lowercase only. Do you have any suggestions. Here is the code as it stands currently.

Public Sub AcrNimTst()
Dim wkbObj As Workbook
Dim arrWords(1 To 1145)
Dim i As Integer
Dim password As String
    password = "123faQ877fhS"
       Set wkbObj = GetObject("PATH")

  For i = 1 To 1145
    arrWords(i) = wkbObj.Worksheets(1).Range("A" & i + 1).Value
       If InStr(1, password, arrWords(i)) > 0 Then 'need to make search not case sensitive
 Exit Sub
   End If
     Next i
End Sub

[DataMiser]

Note the syntax I used on the previous post which is the proper way to make it ignore case.

In your case you need to add the 4th optional parameter of vbtextcompare

If InStr(1, password, arrWords(i),vbTextCompare) > 0 Then 'need to make search not case sensitive

You can also use ucase() around both strings in the statement as in

If InStr(Ucase(password), Ucase(arrWords(i))) > 0 Then

Either way will work but the top one is the one I would use

[Mekinnik]

DataMiser,
Thanks for the help. Another quick question, why is it that when all my functions have returned true, the textbox will not show the password. Here is my code. I have it set up to capture errors, so I know it is completing all the functions, I have also walked thru ot to the end, but it still will not populate the textbox. Any ideas?

password = GetPassword()
     If LwrltrCasetest(password) And UprltrCasetest(password) And _
         NumeralTest(password) And SpecialChrTest(password) And _
          AlphaChrTest(password) And AcrNimTst(password) Then
          
      TxtBx.Text = password

[DataMiser]

Does password still contain a value after the last function returns?
Does the line that assigns the txtBx.Text get executed?

Personally I would not group all of these function calls into a single statement with the AND operator. Kinda hard to read and hard to debug or at least more time consuming to debug. Not sure if all the functions will execute when one fails under these conditions or not as I have never tried it this way.

[Mekinnik]

DataMiser,

Does password still contain a value after the last function returns?
Does the line that assigns the txtBx.Text get executed?

To answer your question, When I step thru the code, and all the functions return true, the line shows that password contains the value generated.

TxtBx.Text = password

However, either it is not executing properly or it is not assigning the value for password, because when I debug it, the value shows in the string but not the TxtBx.txt. TxtBx.txt just show "". For some unforeseen reason it is not assigning the string.

[WizBang]

I think you'll have better results with something like the following:

password = GetPassword()
If Casetest(password) Then
    If NumeralTest(password) Then
        If SpecialChrTest(password) Then
            If AlphaChrTest(password) Then
                If AcrNimTst(password) Then
                    TxtBx.Text = password
                End If
            End If
        End If
    End If
End If

BTW, from the code you posted, it appears you're testing upper and lower case separately. That shouldn't be necessary.

Also, to compare the password against the list of words, it might help to use the Like operator. Also, since the password has already passed the case tests, you could compare it in upper or lower case only, against a list which is all in the same case, thus eliminate that possibility if need be. It just depends on the method you use to make the comparison.

[Mekinnik]

At first I thought the same, but someone mentioned to me that it was a nested if statement, maybe I misunderstood them, so I changed it to the statement I have now. I do agree with and like the readability of your statement. I will give it a try to see if it will work for me.

Thanks WizBang

[dglienna]

So I guess you have 4 different modules to validate the pw? You should have a function that does that that returns True or False.

If ValidPW(text1.text) then Msgbox "Valid"

[Bezzie]

Here is the code as it stands currently.

Public Sub AcrNimTst()
Dim wkbObj As Workbook
Dim arrWords(1 To 1145)
Dim i As Integer
Dim password As String
    password = "123faQ877fhS"
       Set wkbObj = GetObject("PATH")

  For i = 1 To 1145
    arrWords(i) = wkbObj.Worksheets(1).Range("A" & i + 1).Value
       If InStr(1, password, arrWords(i)) > 0 Then 'need to make search not case sensitive
 Exit Sub
   End If
     Next i
End Sub

Your AcrNimTst() is a Sub and not a Function and it is not returning anything so that part of the If... And...And...And...End If will not be True or False so your textbox assignment doesn't get executed because the whole if statement will be False because one of the conditions is not True or False.

Furthermore your assigning a password to the variable you declared in the Sub. The password you tried to pass from the If...And...And...End If statement doesn't get passed into the Sub in the first place.

You have to change it to a Function that will return True or False.

Bezzie

[Bezzie]

Another thing I just noticed is that you are creating a array with 1145 elements, your storing the acronyms from the spreadsheet to this array one at a time as you test them but you are never using it again. You can omit the array all together and just read the string from the spreadsheet into a temporary variable, test it and go on to the next one.

Bezzie