How do i create variable from inputbox

Question

I am new at vba and i want to make a macro for my daily routine. I successfully manage big part of them with your helps here, but i stuck a section.

What i want : I have a analysis file, and it comes with months, beginning of code i am asking how many months have in file and want to know which of them.

    Sub Makro2()

howmanymonths= Application.InputBox(prompt:=ActiveSheet.Name & " how many months?", Type:=1)

For first = 1 To howmanymonths

nay = Application.InputBox(prompt:=ActiveSheet.Name & " First?", Type:=1)

Next first 

End Sub

For example, there are totally 3 months and i answered first "3",then next question i replyed "7" so nay = 7, but when its in loop next will be "8" and "9"

but how i can create nay1, nay2,nay3 or till what last months.

Maybe a better solution? Please help me.

Welcome to SO. I'm sorry but I cannot understand properly your question. I got the part where you ask how many months, but I do not uderstand the nay part. You created a loop that will ask you the value of nay for every month. What exactly do you want to do? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jul 26, 2018 at 9:10
Use Dictionary to store values. dict.add Key, Value. you can retrieve values by key as dict(keys) — nagarajannd
– nagarajannd, Commented Jul 26, 2018 at 9:14
yes @FoxfireAndBurnsAndBurns "nay" is a variable, it become nay1 nay2 nay3.... — Mustafa
– Mustafa, Commented Jul 26, 2018 at 9:18
@nagarajannd sorry i am new didnt understand dict.add Key? How i can use in code? — Mustafa
– Mustafa, Commented Jul 26, 2018 at 9:19
You need an Array then :) Check this-> excelmacromastery.com/excel-vba-array — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jul 26, 2018 at 9:20

nagarajannd · Accepted Answer · 2018-07-26 10:19:17Z

1

Here is the example for storing multiple data in dictionary and retrieve them using their key.

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
howmanymonths = Application.InputBox(prompt:=ActiveSheet.Name & " how many months?", Type:=1)

For first = 1 To howmanymonths
    'Key is nay + numberindex
    dict.Add "nay" & CStr(first), Application.InputBox(prompt:=ActiveSheet.Name & " First?", Type:=1)
Next first

MsgBox dict("nay1")
MsgBox dict("nay2")
MsgBox dict("nay3")

answered Jul 26, 2018 at 10:19

nagarajannd

7155 silver badges11 bronze badges

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

Comments

Vityata · Accepted Answer · 2018-07-26 14:44:16Z

1

You cannot create new variables in a sub, while running the sub. And in general you do not need to. If you do not know how many units do you need, you may use an Array() or List() and add elements to them:

Sub TestMe()

    Dim howManyMonths As Long
    howManyMonths = Application.InputBox(prompt:=ActiveSheet.Name & Months count?",Type:=1)

    Dim firstMonth As Long
    firstMonth = Application.InputBox(prompt:=ActiveSheet.Name & " First?", Type:=1)

    Dim someArray() As Long
    Dim cnt As Long

    ReDim someArray(howManyMonths - 1)

    For cnt = LBound(someArray) To UBound(someArray)
        someArray(cnt) = firstMonth + cnt
    Next cnt

    For cnt = LBound(someArray) To UBound(someArray)
        MsgBox someArray(cnt)
    Next cnt

End Sub

edited Jul 26, 2018 at 14:44

answered Jul 26, 2018 at 9:18

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

Mustafa Over a year ago

I guess this is what i look for, i am trying it, thank you

Collectives™ on Stack Overflow

How do i create variable from inputbox

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related