All geek questions in one place

Using a sheet in excel user-defined function

The VBA I'm trying to write is pretty simple, but Ive never written VBA and never came from visual studio and the C # world, this is really hell!

So I will be grateful for any help / pointers / tips here

alt text

So, I have two important sheets. The range sheet has 2 values ​​per day. He needs a result, the Calc sheet takes two values ​​and gives me the result.

I want to put Current and OneYear values for each date in a Calc sheet and get the result in the results column.

Therefore, I tried to define UDF, but later found out that I could not change the contents of the sheet in UDF.

I tried to record a macro and then hoped from there, but I got something like this:

Range("A2").Select
Sheets("24Feb05-24FEB10").Select
Range("K9").Select
ActiveCell.FormulaR1C1 = "=Calc!R[-8]C[-10]"
Range("K9").Select

Im, , R1C1 , , -8 -10!??

- ?

. 2000 + .

, !

:

Im , Calc, 5 ( ). 4 , -- .

, , #.

UDF, , , , , UDF .

Function Func1(txt As Variant) As String
//Returns the nth element from a delimited text string
    Dim txt As String
    txt = text
   Sheet4.Cells(1, 1) = txt
   Func1 = Sheet4.Cells(2, 1)//get the result value'
End Function

, / VBA API.

, , , , ? , !?

+3
3

, , . , , , , Google, "" .. . Excel , , , #.

, , Excel-:

1) . , , , . , , .

=B2+C2

E2, E. Excel , E3 = B3 + C3 ..

. Excel , .

2) UDF VBA . UDF - , . . UDF, - . ( , forumla Range Excel " " DRY-).

, UDF ( , ), ​​ VBA:

Public Function myUDF(current, oneYear)
    myUDF = current + oneYear
End Function


=myUDF(B2, C2)

E2 E.

, , . , "Excel", UDF. , Excel, .

EDIT: 2.5) Excel. , , , , , , Excel "-", . " " , , , . Excel, , , . :

http://office.microsoft.com/en-us/excel-help/calculate-multiple-results-by-using-a-data-table-HP010072656.aspx

:

- , , (: A , , , . (=).) . .

3) , , . , , VBA, , Excel, VBA . , :

Public Sub doCalc()
    'for each cell in the result column
        'copy the corresponding current and oneYear cells to the calc sheet
        'Excel will recalc the calc sheet
        'copy the result cell on the calc sheet back to the Range sheet in the right place
    'end
End Sub

"Excel-", , . "", . ( , Worksheet.)

, Excel, , Excel , , - .

:

O.K., , (3) , , VBA #. (, , EH ..) , , VBA:

-, VBA, UDF. , Sub, , . , - :

Public Sub fakeFunct(input1, input2, outputRng As Range)
    CalcSheet.[b1] = input1
    CalcSheet.[b2] = input2

    'Excel recalcs what in cell B3 if you have it set to automatic recalcualation

    outputRng = CalcSheet.[b3]
End Sub

"CalcSheet" - " " . ( . -: http://www.cpearson.com/excel/codemods.htm)

, "":

Public Sub loopFakeFunct()
    Dim i As Long
    For i = 2 To 2000
        Call fakeFunct(RangeSheet.Cells(i, 2), RangeSheet.Cells(i, 3), RangeSheet.Cells(i, 5))
    Next i
End Sub

, , Worksheet_Change .. , VBA, , . ( "RangeSheet" "Range" , Range Application.Range, Excel.)

4) , UDF, (2). , , , , ++ - , XLL.

5) , Microsoft:

http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel.htm

6) Resolver One:

http://www.resolversystems.com/products/resolver-one/

, , , Python.

+3

"" . R [-8] C [-10] : 8 10 .

+1

, Excel, #: , UDF, Excel, : / , , , ? VBA, J Tolle , .

0
source

Source: https://habr.com/ru/post/1784390/

More articles:


All Articles