Clever Geek Handbook

How to call a worksheet function from a word

I am trying to find the maximum of a column in excel from a word, the previous one I used

MyResult = application.Worksheetfunction.max (Range("B4:B7"))

from excel, now i need to do similar from vba in word.

But I can't figure out how to do it from word, I need to make my own loop and create my own max function?

maxVal =0
for i=2 to lastrow
    if xlapp.ws.cells(i,2)>maxVal then
      maxVal= xlapp.ws.cells(i,2)
    end if
 next i
+3


source to share


2 answers


Here is my code based on input from AnlaystCave:



val(oXLApp.Worksheetfunction.Max( _
                xlApp.Sheets("List").Range(xlApp.Sheets("List").Cells(3, columnWp), xlApp.Sheets("List").Cells(numofrows, columnWp))))
0


source


First I will answer the question you asked:

How to call a worksheet function from a word

Same:



 Dim excelApp As Object
 Set excelApp = CreateObject("Excel.Application")
 maxVal = excelApp.Worksheetfunction.max(1, 2, 3) 'Some values separated by comma or an array
 excelApp.Quit

Now you only need the Max function. For example:

Function MaxFunc(arr)
    Dim maxVal, tmpVal, ifFirst
    ifFirst = True
    For Each it In arr
        If ifFirst Then
            maxVal = it: ifFirst = False
        Else
            If maxVal < it Then maxVal = it
        End If
    Next
    MaxFunc = maxVal
End Function
0


source






More articles:


All Articles