• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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!
0
stmoritz
Asked:
stmoritz
  • 4
  • 4
2 Solutions
 
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
 
Saqib Husain, SyedEngineerCommented:
Can you give me examples of two correct codes you enter?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
stmoritzAuthor Commented:
APPLE7
SUN9GOLD
0
 
Saqib Husain, SyedEngineerCommented:
Change the following lines

                  Call EnterAndValidateCode

Sub EnterAndValidateCode


to

                  Call EnterAndValidateCode(activecompany)

Sub EnterAndValidateCode(activecompany As String)
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, SyedEngineerCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now