Excel VBA variable value "" although string entered

This code checks whether there's a string in cell B41 of the source sheet, if yes, it takes it as ActiveCompany variable, ...

'get Company Code to process
ActiveCompany = SourceWs.Range("B41").Value
If ActiveCompany = "" Then
                  Call EnterAndValidateCode
                  SourceWs.Range("B41") = ActiveCompany
End If

Open in new window


...if not, a subroutine is called that asks the user to enter the string which is then validated and should be written/sent as variable value to ActiveCompany:

Sub EnterAndValidateCode()
    
    Dim ActiveCompany As String
    Dim nameOk As Boolean
    nameOk = True
    Do
        ActiveCompany = InputBox("Please assign a 10 character alphanumeric Code for " & ActiveSheet.Range("D2"), "Code")
        If (ActiveCompany <> "") Then nameOk = ValidateName(ActiveCompany)
    Loop While (nameOk = False) And (ActiveCompany <> "")
End Sub
Function ValidateName(ActiveCompany As String) As Boolean
    Dim bValid As Boolean
    Dim iCharCntr As Integer
    Dim strChar As String
    
    bValid = True ' assume true until proven wrong
    
    ' validation 1: cannot exceed 10 letters/digits
    If Len(ActiveCompany) > 10 Then
        '
        bValid = False
    Else
        ' validation 2: only allowed to contain letters or numbers
        For iCharCntr = 1 To Len(ActiveCompany)
            strChar = Mid(ActiveCompany, iCharCntr, 1)
            Select Case strChar
                Case "A" To "Z", "a" To "z", "0" To "9"
                    ' this is okay so do nothing
                Case Else
                    bValid = False
                    Exit For
            End Select
        Next
    End If
    
    ValidateName = bValid
End Function

Open in new window


However, something is wrong, as even if entered a correct code, ActiveCompany remains ""

Any help appreciated!
stmoritzAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Change the following lines

                  Call EnterAndValidateCode

Sub EnterAndValidateCode


to

                  Call EnterAndValidateCode(activecompany)

Sub EnterAndValidateCode(activecompany As String)
0
 
Saqib Husain, SyedEngineerCommented:
Where do you enter the correct code?
0
 
stmoritzAuthor Commented:
In this input box:

ActiveCompany = InputBox("Please assign a 10 character alphanumeric Code for " & ActiveSheet.Range("D2"), "Code")
        If (ActiveCompany <> "") Then nameOk = ValidateName(ActiveCompany)

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Saqib Husain, SyedEngineerCommented:
Can you give me examples of two correct codes you enter?
0
 
stmoritzAuthor Commented:
APPLE7
SUN9GOLD
0
 
stmoritzAuthor Commented:
Thanks. When I start the code I get "Compile error: Duplicate declaration in current scope".

Should I use another variable than ActiveCompany for the validation subroutine as I already use it as ActiveCompany in the code?
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
comment out the dim statement in the Sub EnterAndValidateCode

and make sure that the variable is dimensioned as a string in the main sub
0
 
stmoritzAuthor Commented:
perfect! I am so happy! thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.