Excel VBA: Create Dynamic data validation formula-CodePudding

I have a below formula which validates for IP address in Excel cell and works fine.

Dim cellAddress as Variant 

cellAddress =Target.value 'Target is a Range

=AND(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[.*1>-1][.*1<256]"))=4,LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3)

My problem is that I want to pass the cellAddress as a Dynamic value to my formula instead of 'A1'

Can somebody guide

CodePudding user response：

You can create a Public Function that returns a Boolean, and pass in the cell reference as a Range to the function.

Here's an example of the code:

Option Explicit

' =AND(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[.*1>-1][.*1<256]"))=4,LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3)

Public Function ValidateIPAddress(source As Range) As Boolean
    ' Get the first condition's value...
    Dim xmltxt As String
    xmltxt = "<t><s>" & Application.WorksheetFunction.Substitute(source.Value, ".", "</s><s>") & "</s></t>"

    Dim sections As Variant
    sections = Application.WorksheetFunction.FilterXML(xmltxt, "//s[.*1>-1][.*1<256]")
    
    ' Get the second condition's value...
    Dim separators As Integer
    separators = Len(Application.WorksheetFunction.Substitute(source.Value, ".", ""))

    ' Compare both conditions and return the result
    ValidateIPAddress = Application.WorksheetFunction.Count(sections) = 4 And Len(source.Value) - separators = 3
End Function

And here's a snippet of the usage:

I tried to break out the formula you provided to make it legible without spending too much time on it. It can definitely be improved or you can even nest everything into one line if you wanted to, but I wouldn't recommend that because it'll be difficult to debug.

EDIT:

If you just wanted to replace the "A1" string you can concatenate the cellAddress variable with parts of the formula.

Here's a code snippet example:

Dim validationFormula As String
validationFormula = "=AND(COUNT(FILTERXML(""<t><s>""&SUBSTITUTE(" & _
    Target.Value & ",""."",""</s><s>"")&""</s></t>"",""//s[.*1>-1][.*1<256]""))=4,LEN(" & _
    Target.Value & ")-LEN(SUBSTITUTE(" & _
    Target.Value & ",""."",""""))=3)"
    
Debug.Print validationFormula

Output of the formula when changing a cell's value to "A5":

Note: You have to replace all double quotes with 2 double quotes when handling quotes in strings.