How to use variable inside a variable in excel macro [duplicate]

Question

I have a chunk of code that is calling a function insertIntoPlannerFile to do some updating as below

 Call insertIntoPlannerFile("supplier 1 po", rowNumber, prForm.po1Edit)
 Call insertIntoPlannerFile("supplier 1 po date", rowNumber, prForm.po1DateEdit)

Here prForm is the form name and po1Edit is the name of a textbox in that form. I have some other textbox also in the form such as po2Edit, po3Edit etc.

Since the above chunk of code is re using, I tried to make this also as a function. So I type the code as below

Function insertModule(objectNumber As String)
      Call insertIntoPlannerFile("supplier " & objectNumber & " po", rowNumber, prForm.po & objectNumber & Edit)
      Call insertIntoPlannerFile("supplier " & objectNumber & " po date", rowNumber, prForm.po & objectNumber & DateEdit)
End Function

And called the function as below

Call insertModule("1")
Call insertModule("2")

But somehow it is not working. Does anyone know how to change it into a re usable function?

I guess you need to put Edit resp DateEdit into quotes ("Edit" resp. "DateEdit") — FunThomas
– FunThomas, Commented Sep 25, 2020 at 7:11
This question has been asked so many times in the past. Please search stackoverflow — Siddharth Rout
– Siddharth Rout, Commented Sep 25, 2020 at 7:11
@FunThomas No, variable names cannot be variable they have to be hardcoded. — Pᴇʜ
– Pᴇʜ, Commented Sep 25, 2020 at 7:12
@PEH, sorry, you are right, didn't read the question carefully enough. — FunThomas
– FunThomas, Commented Sep 25, 2020 at 7:14

Pᴇʜ · Accepted Answer · 2020-09-25 07:09:54Z

1

If you want the numeber in prForm.po1Edit being variable, the only way to do this is using Controls.

prForm.Controls("po" & NumberVariable & "Edit")

because variable names cannot be dynamic.

Note that your Function does not return any values, therefore it should be a procedure Sub:

Sub insertModule(objectNumber As String)
      Call insertIntoPlannerFile("supplier " & objectNumber & " po", rowNumber, prForm.Controls("po" & objectNumber & "Edit"))
      Call insertIntoPlannerFile("supplier " & objectNumber & " po date", rowNumber, prForm.Controls("po" & objectNumber & "DateEdit"))
End Sub

answered Sep 25, 2020 at 7:09

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

How to use variable inside a variable in excel macro [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related