what does the follow Dim's mean

[Danny54]

I have some code provided here and trying to understand and learn from the examples which are great. Can someone explain the following Dim values

Dim S$(), N&, A%, T$(3), b%
for the above statement what do these do?
S$()
N&
A%
T$(3)
b%

W = [Sheet1!A1].CurrentRegion.Value2
at the conclusion of this statement what values is actually stored in W?

ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)
I know this reDim S(1,Value) but what do these do?
S(1, 0) = W(1, 1):
S(1, 1) = W(1, 2)

Any help would be greatly appreciated





The code this is pulled from looks like this

Sub Demo2()
Dim W, S$(), N&, K&, V, L$(), r$(), A%, T$(3), b%, C%, D%
W = [Sheet1!A1].CurrentRegion.Value2
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)
N = 1
For K = 2 To UBound(W)
For Each V In Split(W(K, 2), ",")
L = Split(V, "-")
If UBound(L) = 1 Then
r = Split(L(1), ".")
L = Split(L(0), ".")
If UBound(L) = 3 And UBound(r) = 3 Then
For A = L(0) To r(0)
T(0) = A
For b = -L(1) * (A = L(0) * 1) To IIf(A < r(0) * 1, 255, r(1))
T(1) = b
For C = -L(2) * (b = L(1) * 1) To IIf(b < r(1) * 1, 255, r(2))
T(2) = C
For D = -L(3) * (C = L(2) * 1) To IIf(C < r(2) * 1, 255, r(3))
T(3) = D
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = Join(T, ".")
Next D, C, b, A
End If
Else
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = V
End If
Next V, K
[Sheet1!A1:B1].Resize(N).Value2 = S
End Sub

 

[Jon Peltier]

The shorthand for the types is: % -integer; & -long; @ -currency; # -double; ! -single; $ -string Declaring variables (VBA)

Let me just say that using these suffix characters is not really a helpful shortcut. It encourages laziness, and makes it hard for other users to read the code (as the initial question has proven).

 

[Danny54]

Thanks Eric

here's what works
Sheet2

source	ip_addresses
A	169.254.1.168,172.26.38.168
B	172.26.38.167,169.254.1.167-169.254.1.172,192.26.38.200

Output
Sheet3

source	ip_addresses
A	169.254.1.168
A	172.26.38.168
B	172.26.38.167
B	169.254.1.167
B	169.254.1.168
B	169.254.1.169
B	169.254.1.170
B	169.254.1.171
B	169.254.1.172
B	192.26.38.200

The following macro reads sheet2, cycles thru the address range(ip_addresses) and expands the ip range to build unique rows for each new ip in the range

Sub Expandit()
Dim W, S$(), N&, K&, V, L$(), r$(), A%, T$(3), b%, C%, D%
W = [Sheet2!A1].CurrentRegion.Value2
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)
N = 1
For K = 2 To UBound(W)
For Each V In Split(W(K, 2), ",")
L = Split(V, "-")
If UBound(L) = 1 Then
r = Split(L(1), ".")
L = Split(L(0), ".")
If UBound(L) = 3 And UBound(r) = 3 Then
For A = L(0) To r(0)
T(0) = A
For b = -L(1) * (A = L(0) * 1) To IIf(A < r(0) * 1, 255, r(1))
T(1) = b
For C = -L(2) * (b = L(1) * 1) To IIf(b < r(1) * 1, 255, r(2))
T(2) = C
For D = -L(3) * (C = L(2) * 1) To IIf(C < r(2) * 1, 255, r(3))
T(3) = D
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = Join(T, ".")
Next D, C, b, A
End If
Else
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = V
End If
Next V, K
[Sheet3!A1:B1].Resize(N).Value2 = S

MsgBox "Complete"
End Sub


All the above works in the creation of Sheet2



Now in comes a new formatted sheet where the column ip_address is no longer in col b but now in col c. Additionally data in col d

source	start	ip_addresses	owner
A	N	169.254.1.168,172.26.38.168	John
B	Y	172.26.38.167,169.254.1.167-169.254.1.172,192.26.38.200	Joe

The modifications I'm trying to make
1. use column c for finding the ip_addresses (expand the addresses)
2. create a new sheet with all the data found in each column where a new line is created by ip_count

source	start	ip_addresses	owner
A	Y	169.254.1.168	John
A	Y	172.26.38.168	John
B	N	172.26.38.167	Joe
B	N	169.254.1.167	Joe
B	N	169.254.1.168	Joe
B	N	169.254.1.169	Joe
B	N	169.254.1.170	Joe
B	N	169.254.1.171	Joe
B	N	169.254.1.172	Joe
B	N	192.26.38.200	Joe

As you can tell, I'm not real good with arrays but not afraid of trying.
What you provided has been very instrumental in this endeavor.

Thanks

 

[Eric W]

but not afraid of trying.

Good! Learning by doing is always best.

Just FWIW, here's how I would write that macro. Maybe you can get a few ideas from it. Also note that I used code tags to make it easier to read.

Sub ExpandIP()
Dim RawData As Variant, DataRow As Long, IPs As Variant, IP As Variant, IPRange As Variant
Dim OutData() As Variant, OutRow As Long, LowVal As Long, HighVal As Long, i As Long

' Read the input data and set up the output array
    RawData = Range(Range("A1"), Range("D1").End(xlDown)).Value
    ReDim OutData(1 To Rows.Count, 1 To 4)
    OutRow = 1
    
' Move the headers
    For i = 1 To 4
        OutData(1, i) = RawData(1, i)
    Next i
    
' Check each row of data
    For DataRow = 2 To UBound(RawData)
        IPs = Split(RawData(DataRow, 3), ",")
        For Each IP In IPs
            IPRange = Split(IP, "-")
            LowVal = Split(IPRange(0), ".")(3)
            HighVal = Split(IPRange(UBound(IPRange)), ".")(3)
            For i = LowVal To HighVal
                OutRow = OutRow + 1
                OutData(OutRow, 1) = RawData(DataRow, 1)
                OutData(OutRow, 2) = RawData(DataRow, 2)
                OutData(OutRow, 3) = Left(IPRange(0), InStrRev(IPRange(0), ".")) & i
                OutData(OutRow, 4) = RawData(DataRow, 4)
            Next i
        Next IP
    Next DataRow
    
' Add a new sheet and write out the results
    Sheets.Add
    Range("A1").Resize(OutRow, 4) = OutData
            
End Sub

 

[Danny54]

Good! Learning by doing is always best.

Just FWIW, here's how I would write that macro. Maybe you can get a few ideas from it. Also note that I used code tags to make it easier to read.

Sub ExpandIP()
Dim RawData As Variant, DataRow As Long, IPs As Variant, IP As Variant, IPRange As Variant
Dim OutData() As Variant, OutRow As Long, LowVal As Long, HighVal As Long, i As Long

' Read the input data and set up the output array
    RawData = Range(Range("A1"), Range("D1").End(xlDown)).Value
    ReDim OutData(1 To Rows.Count, 1 To 4)
    OutRow = 1
   
' Move the headers
    For i = 1 To 4
        OutData(1, i) = RawData(1, i)
    Next i
   
' Check each row of data
    For DataRow = 2 To UBound(RawData)
        IPs = Split(RawData(DataRow, 3), ",")
        For Each IP In IPs
            IPRange = Split(IP, "-")
            LowVal = Split(IPRange(0), ".")(3)
            HighVal = Split(IPRange(UBound(IPRange)), ".")(3)
            For i = LowVal To HighVal
                OutRow = OutRow + 1
                OutData(OutRow, 1) = RawData(DataRow, 1)
                OutData(OutRow, 2) = RawData(DataRow, 2)
                OutData(OutRow, 3) = Left(IPRange(0), InStrRev(IPRange(0), ".")) & i
                OutData(OutRow, 4) = RawData(DataRow, 4)
            Next i
        Next IP
    Next DataRow
   
' Add a new sheet and write out the results
    Sheets.Add
    Range("A1").Resize(OutRow, 4) = OutData
           
End Sub

Sweet Eric

thanks so much. I see how your macro is easier to read which makes it easier to understand. Many things to take in consideration