使用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屋！