VBA Create an array with numbers 1 through some calculated value

Question

Question:

How can I create the following array without knowing the ending number before hand:

myArray = Array(0, 1, 2, 3, 4)

For example, I can do something similar in Python:

myList = range(ending_number)

As well as in Matlab:

myVector = 0:ending_number

More Detail:

I have a path being specified in an Excel worksheet that references a location on a Linux server. I want the user to only have to specify the path in terms that the Linux server understands, but also need to reference a location in terms that both the local Windows machine and the Linux server understands. The Linux server path is specified as

/home/shelf6/some/path/to/a/location/on/the/server

and the corresponding path in Windows is specified as

\\SB1\home_shelf6\some\path\to\a\location\on\the\server

I am currently converting from one to the other with the following commands:

WinJobLoc = Split(LinJobLoc, "/", -1, vbTextCompare)
WinJobLoc = "\\sb1\" & WinJobLoc(1) & "_" & WinJobLoc(2) & "\" & _
             Join(Application.WorksheetFunction.Index(WinJobLoc, 0, _
             Array(4, 5, 6, 7, 8, 9, 10, 11)), "\", vbTextCompare)

However, since I will only be able to know the length of the path from UBound(WinJobLoc), I don't know of an easy way to get the portion of the path following "shelf6."

Is there some way to do what I'm trying to do (i.e. without the hard-coded array), or is there an easier way to do the whole thing?

Gary's Student · Accepted Answer · 2014-01-16 15:42:19Z

2

Consider:

Sub ArrayCreator()
    Dim N As Long, i As Long
    N = Application.InputBox(Prompt:="Enter a number", Type:=1)
    ReDim myarray(0 To N) As Long
    For i = 0 To N
        myarray(i) = i
    Next i
End Sub

answered Jan 16, 2014 at 15:42

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tlewis3348 Over a year ago

Indeed this accomplishes what I was trying to do, but I was hoping to be able to find the most concise means of accomplishing my ultimate purpose.

user2140261 · Accepted Answer · 2014-01-16 16:08:54Z

0

I could be reading the question wrong but should this be enough:

Sub stringModify()
Dim LinJobLoc As String
Dim WinJobLoc As String

LinJobLoc = "/home/shelf6/some/path/to/a/location/on/the/server"

LinJobLoc = Replace(Right(LinJobLoc , Len(LinJobLoc ) - 1), "/", "\")

WinJobLoc = "\\SB1\" + Replace(LinJobLoc, "\", "_", , 1)
End Sub

But this does not answer your question to answer that you have two options:

One is you use a ReDim on the Array

The second option is to not use an array but instead use a collection.

edited Jan 16, 2014 at 16:08

answered Jan 16, 2014 at 15:53

user2140261

8,0037 gold badges35 silver badges45 bronze badges

3 Comments

tlewis3348 Over a year ago

This is nice, but did you intend the first LinJobLoc to be s1 instead? If not, please specify what s1 is so I can mark your solution as the answer.

user2140261 Over a year ago

Oh yeah that was testing then I added in your values. it is updated now, I used s1 as String 1 in testing.

tlewis3348 Over a year ago

It would be simpler if the WinJobLoc command were WinJobLoc = "\\SB1\" + Replace(Replace(LinJobLoc, "/", "\"), "\", "_", 2, 1). This would also allow LinJobLoc to be referenced later (assuming that the second LinJobLoc command were removed since it would be unnecessary with that change).

Alex K. · Accepted Answer · 2014-01-16 15:51:50Z

0

Just re-read from the linux path, starting from an offset equal to the sum of the lengths of the elements you have already used, plus slashes, swapping path chars;

Dim ArrWinJobLoc() As String, WinJobLoc As String

ArrWinJobLoc = Split(LinJobLoc, "/")

WinJobLoc = "\\sb1\" & ArrWinJobLoc(1) & "_" & ArrWinJobLoc(2) & _
    Replace$(Mid$(LinJobLoc, 3 + Len(ArrWinJobLoc(1)) + Len(ArrWinJobLoc(2))), "/", "\")

answered Jan 16, 2014 at 15:51

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

VBA Create an array with numbers 1 through some calculated value

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related