EXCEL VBA-CodePudding

I need to write a Function to realize such functions,
The Function Func1 (RNG As Object) As the Variant
Multiple integer input is selected in the cell, such as 12, through calculation to get four Double,
B1: selected B4, type the formula={Func1 (A1: A12)}, four Double values in the B1 to B4, how to implement, thank you,

CodePudding user response:

The

refer to the original poster yyh7011cn response:

I need to write a Function, can realize the Function of this,
The Function Func1 (RNG As Object) As the Variant
.

Within the cell shows content, not "value" is the text, so you put the function return value type as the Variant does not make sense,
Should be according to your actual situation to choose the appropriate type, you should be selected as the type Double here,

 Function Func1 (RNG As Range) As a Double 
'I don't know what is your specific algorithm, 
'is made a "numerical summation" simple example 
As Double Dim v 
Dim As Long I 

V=0 # 
For I=1 To RNG. Rows. Count 
V=v + RNG. Cells (I, 1). The Value 
Next 
Func1=v 
End the Function

The

refer to the original poster yyh7011cn response:

...
Multiple integer input is selected in the cell, such as 12, through calculation to get four Double,
Selected B1: B4, type the formula={Func1 (A1: A12)}, four Double values in the B1 to B4, how to implement, thank you.

A function can return a value (except returns "array", but may not show in the cell array of "results"),
B1: B4 could not once, the formula of input; You have to at least B1 input formula, and then copy to the following formula 3 cell,
It seems, is actually your "of" a set of data, the calculation of the "four result data, respectively is shown in the four cell",
So, actually you should have to add a function parameter, specify its "return to which a parameter,"
In this way, your function should be defined as:

 Function Func2 (RNG As Range, w As Long) As String 
'to start a series of calculation... 
'... 
'... 
'... 
', according to the w value returns the results of the corresponding 
The Select Case w 
Case 1: Func2="first (1) a result" 
Case 2: Func2="in the first 2 results" 
Case 3: Func2="the first (3) the results 
"Case 4: Func2="the first (4) the results" 
Case the Else: Func2="parameter error" 
End the Select 
End the Function

Then, in the B1 input formula:=Func2 ($A $1: $A $12, ROW ())
Selected B1 cells again, the mouse drag copy formula to the following several cell line,
(note: Func2 () code, you see the change)

CodePudding user response:

I return values cannot be Double, and in fact I input values of 12, after four Double after calculation, I need in the four cell shows the four values, the second method is also a problem, the value of 12, I need to go through complex calculations, the calculation process with the realization of DLL in C, so it is impossible for me to call four times, too time consuming,

CodePudding user response:

Since you want to use "formula", then the function is called several times is inevitable,
Excel spreadsheet is the good sample,

If you want to improve their efficiency, so you don't use formula,
Draw a button in the table, write the corresponding event code,
If the data source location and area, the output data are fixed position, then the event direct call process,
Simple said, is directly in the process, from A1 to A12 read data, call DLL is calculated, and then fill to B1 to result in B4,
If the data source is not fixed, so in the process of button events, with the input box to ask data source area, the output position,
Ask the data source, for example, you enter A1: A12, then in the process of reading data from A1 to A12;
Ask output location, B1 you input, so the calculation results from B1, four cell in a continuous output the four data,
The rest of the analogy,