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

 

[JEC]

What is the code for? Probably it can be written easier with better readability.
About the Dim statements, you can see these in the local variable window.

 

[Danny54]

The code takes in a sheet and expands out the values between IP ranges found in column b.

Title	Ranges
A	10.10.10.1, 10.10.10.4-10.10.10.7
B	10.10.10.20
C
D	10.10.10.240-10.10.10.241
E	10.10.10.250, 10.10.10.251, 10.10.10.252

New Output Sheet

Title	Ranges	Expanded
A	10.10.10.1, 10.10.10.4-10.10.10.7	10.10.10.1, 10.10.10.4, 10.10.10.5, 10.10.10.6, 10.10.10.7
B	10.10.10.20	10.10.10.20
C
D	10.10.10.240-10.10.10.241	10.10.10.240, 10.10.10.241
E	10.10.10.250,10.10.10.251,10.10.10.252	10.10.10.250, 10.10.10.251, 10.10.10.252

https://www.mrexcel.com/board/posts/5789754/mark-solution

 

[Danny54]

What im trying to find out is


S$() <- is this allocating a string array
N& <- dont know what this allocates
A% <- dont know what this allocates
T$(3) <-assume this allocates a string array of 3 characters
b% <- dont know what this allocates

 

[jalrs]

What im trying to find out is


S$() <- is this allocating a string array
N& <- dont know what this allocates
A% <- dont know what this allocates
T$(3) <-assume this allocates a string array of 3 characters
b% <- dont know what this allocates

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

 

[Eric W]

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

The Dim statement defines variables for use in the macro. It's not strictly necessary in VBA, since any undefined variable is automatically defined as Variant, but it's good programming practice to do so. In fact an Option Explicit line should precede your procedure to make it necessary. The character following each variable tells you what type the variable is. See here:

Type Characters - Visual Basic

Learn more about: Type characters (Visual Basic)

docs.microsoft.com

So S$() is defining an array with currently undefined dimensions, and the $ means it will contain string values. (The ReDim later on will define the dimensions.) An ampersand (&) is a Long Integer, and a % is regular Integer. You could also write the Dim like this:

Dim S() as String, N as Long, A as Long, T(3) as String, b as Integer

 

[Danny54]

The Dim statement defines variables for use in the macro. It's not strictly necessary in VBA, since any undefined variable is automatically defined as Variant, but it's good programming practice to do so. In fact an Option Explicit line should precede your procedure to make it necessary. The character following each variable tells you what type the variable is. See here:

Type Characters - Visual Basic

Learn more about: Type characters (Visual Basic)

docs.microsoft.com

So S$() is defining an array with currently undefined dimensions, and the $ means it will contain string values. (The ReDim later on will define the dimensions.) An ampersand (&) is a Long Integer, and a % is regular Integer. You could also write the Dim like this:

Dim S() as String, N as Long, A as Long, T(3) as String, b as Integer

Thanks. Didn't know there was a shorthand ability. Makes since.

 

[Danny54]

on more question

ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)

if the array S is being dimensioned to the size of the sheet (right?)

Then S(1,0) in the newly defined array is assigned to the values currently found at W(1, 1) and
the same for S(1,1) to whats found in W(1,2)

When I step thru the code thats what I see

Now if i wanted to get the next values in the row which is column c I would assume I could do this
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2) : S(1, 2) = W(1, 3)

 

[Danny54]

The Dim statement defines variables for use in the macro. It's not strictly necessary in VBA, since any undefined variable is automatically defined as Variant, but it's good programming practice to do so. In fact an Option Explicit line should precede your procedure to make it necessary. The character following each variable tells you what type the variable is. See here:

Type Characters - Visual Basic

Learn more about: Type characters (Visual Basic)

docs.microsoft.com

So S$() is defining an array with currently undefined dimensions, and the $ means it will contain string values. (The ReDim later on will define the dimensions.) An ampersand (&) is a Long Integer, and a % is regular Integer. You could also write the Dim like this:

Dim S() as String, N as Long, A as Long, T(3) as String, b as Integer

Thanks Eric

one more question

my code has the following line
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)

if the array S is being dimensioned to the size of the sheet - two columns wide (correct?)

Then S(1,0) in the newly defined array is assigned to the values currently found at W(1, 1) and
the same for S(1,1) to the values found in W(1,2)


So I wanted to get the next values in the row for column c, I would assume I could do this
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2) : S(1, 2) = W(1, 3)

 

[Eric W]

Where did you get that code? The code provided by this site is generally a bit cleaner, although it varies based on who answers the question. For example, I'm not a big fan of using : to put multiple statements on one line (with a few exceptions). It makes it cluttered and harder to read.

Have you tried your sample code? I'm guessing not, since I can tell by looking at it that it won't work. Here are some things to try. When stepping through the macro, after the

W = [Sheet1!A1].CurrentRegion.Value2

line, type

print ubound(W), ubound(W, 2)

in the Immediate window. This will give you the upper bounds of the 1st and 2nd dimensions of W, and you can see what W contains. In this case, the 1st dimension is the number of rows, and the second dimension is the number of columns. So W(3,1) refers to A3.

ReDim S(1 To Rows.Count, 1)

defines the dimensions of S, the first varies from 1 to the number of rows in your workbook, 1048576, and the second varies from 0 to 1. In my opinion, this is bad practice since the lower bound of the 1st dimension is explicitly defined, but the lower bound of the second dimension is assumed. Confusing. Moreover, if you're going to be transferring data from another array, and you're trying to match rows and columns, it would be clearer to match the indexes (if possible). In your suggested code:

ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2) : S(1, 2) = W(1, 3)

I don't know the upper bound of the column dimension of the W array since it varies based on the CurrentRegion, but I do know the upper bound of the column dimension of the S array since you just defined it as 1. So S(1, 2) = W(1, 3) won't work. If you want the next 2 values (A2:B2), it would be:

S(2, 0) = W(2, 1)
S(2, 1) = W(2, 2)

But I would write it as:

ReDim S(1 to UBound(W), 1 to 2)
S(1, 1) = W(1, 1)
S(1, 2) = W(1, 2)
' and if you want the next row
S(2, 1) = W(2, 1)
S(2, 2) = W(2, 2)

Doesn't that seem clearer? All the indices match?

Just reading over the whole macro, it looks like columns A:B are being read into W. S is created to be the output array. The first row of the range (A1:B1) is being moved from W to S, probably headers. Then the data is being parsed somehow from W into S, from row 2 to the end. I can't say how, since I can't see the data. Then S is being rewritten back in place. As JEC mentioned, this whole macro could probably be rewritten to be clearer and more efficient. If you do want a better macro, please provide some sample data and an explanation of what you want done with it. At this point, I'd suggest a new thread, and I wouldn't even show the old code.

Hope this helps!