使用excel宏传递变量以完成句子 [英] Passing variables using excel macros in order to complete a sentence
问题描述
我是excel宏的新手.如果问任何愚蠢的问题,请纠正我.
I am new to excel macros.Please correct me if am asking anything silly.
我想在excel工作表中打印以下CALL语句,其中要对SCHEMA_NAME,PROCEDURE_NAME,INPUT_DATE,EXIT_DATE进行参数化,我想通过按钮从用户那里获取这些参数.
I want to print the below CALL statement in excel sheet where SCHEMA_NAME, PROCEDURE_NAME, INPUT_DATE, EXIT_DATE are to be parameterized and I want to take these parameters from the user through a button.
CALL SCHEMA_NAME.PROCEDURE_NAME('INPUT_DATE','EXIT_DATE',STATUS);
有人可以帮我写一个宏吗?
Can someone please help me to write a macro for this.
到目前为止,我只编写了CONCATENATE语句:
As of now I have written only the CONCATENATE statement:
=CONCATENATE("CALL"," ", "SCHEMA_NAME", ".", "PROCEDURE_NAME", "(", "'", "INPUT_DATE", "'", ",", "'", "EXIT_DATE", "'", ",", "STATUS", ")", ";")
谢谢.
推荐答案
您处在正确的轨道上.您可以像以前一样使用竞争者,也可以使用变量.变量的值可以从任何来源获取.注意:您可以省略Concatenate
,而只需使用&
. (请注意之前和之后的空格.)例如:
You are on the right track. You can use contants as you did and you can use variables. The values of the variables can be taken from any source. N.b.: you can omit Concatenate
and simply use &
. (Note the spaces before and after.) E.g.:
Dim s1 as String, s2 as String, sCommand as String
s1 = Inputbox ("Enter schema name")
s2 = Inputbox ("Enter procedure name")
sCommand = "Call " & s1 & "." & s2 & "('INPUT_DATE','EXIT_DATE',STATUS)"
或者您可以从Excel或Word中获取值:
Or you can take values from Excel or Word:
Dim s1 as String, s2 as String, sCommand as String
s1 = Cells(1, 1) ' schema name
s2 = Cells(2, 1) ' procedure name
sCommand = "Call " & s1 & "." & s2 & "('INPUT_DATE','EXIT_DATE',STATUS)"
根据需要生成命令列表:
To generate a list of commands as you need:
Dim dFrom As Date, dTo As Date, d As Date
Dim iStep As Long ' day
Dim i As Long
dFrom = "2017-01-01"
dTo = "2017-06-30"
iStep = 10 ' days
i = 1 ' start in column A
For d = dFrom To dTo Step iStep
sCommand = "Call " & s1 & "." & s2 & "(" & _
Format(d, "yyyy-mm-dd") & "," & _
Format(d + iStep - 1, "yyyy-mm-dd") & ",STATUS)"
Cells(1, i).value = sCommand
i = i + 1
Next
您可以省略使用sCommand并将字符串直接写到单元格中,但是通过这种方式,您可以更轻松地调试它.
You can omit using sCommand and write the string directly to the cell, yet this way you can debug it easier.
这篇关于使用excel宏传递变量以完成句子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!