none
Excel UDF fill more cells RRS feed

  • 问题

  • UDFs can return only a single value. So which only fill one cell.Whether there are methods When Using UDF which can fill more cells.In Excel VB I defined following

    Public Declare Sub setExcelApp Lib "owndll.dll" (ByVal pExcelApp As Variant)
    Public Declare Function OwnFunction Lib "owndll.dll" (ByVal field As String) As Variant

    I just when using "=OwnFunction()" in Excel,Which can fill more cells,not just one.I try to create thread in the OwnFunction() of Dll ,Which can fill more cell,But later the Excel will crash.Can you give me some advice ,thank you.

    VARIANT  _stdcall OwnFunction (char* pfieldlist)
    {
        MessageBox(NULL,"OwnFunction ","",MB_OK);
        VARIANT v; v.vt=VT_BSTR; v.bstrVal=SysAllocString(L"Hello");
        hThread = CreateThread(NULL,0,(LPTHREAD_START_ROUTINE)ClientThread,NULL,0,&dwThread);
       return v;
            
    }
    DWORD WINAPI ClientThread(LPVOID lpParam)
    {
        CoInitialize(NULL);
        CComQIPtr<MSExcel::_Application> spApp(g_Application);
        CComQIPtr<MSExcel::_Workbook> book;
        book = spApp->ActiveWorkbook;

        CComQIPtr<MSExcel::_Worksheet> sheet;
        sheet = book->ActiveSheet;
        CComQIPtr<MSExcel::Range> range;
        range = sheet->GetCells();//GetUsedRange(NULL);//
        range->Put_Default (_variant_t((long)1),_variant_t((long)1),_variant_t("User1"));
        range ->Put_Default (_variant_t((long)1),_variant_t((long)2),_variant_t("User2"));
        MessageBox(NULL,"ClientThread ","",MB_OK);
        // spApp.Release();
        book.Release();
        sheet.Release();
        range.Release();
        CoUninitialize();
        return 0;
    }


    • 已编辑 moon_lu 2013年8月7日 9:04
    2013年8月5日 9:38

答案

  • Hi moon_lu,

    Sorry for this delayed reply.

    For I don't have enough knowledge of the Excel UDF,I still cannot find out the perfect solution at present.What I can provide here are just some suggestions.

    You said that the function you defined was only able to return a single value,so I think you can try to use the array to implement your requirements.

    As for the problem of “Excel crash”,I think the cause might be that the memory allocation strategy was not the appropriate one.

    The following is a piece of sample code from Creating Threads:

    for( int i=0; i<MAX_THREADS; i++ ) { // Allocate memory for thread data. pDataArray[i] = (PMYDATA) HeapAlloc(GetProcessHeap(), HEAP_ZERO_MEMORY, sizeof(MYDATA)); if( pDataArray[i] == NULL ) { // If the array allocation fails, the system is out of memory // so there is no point in trying to print an error message. // Just terminate execution. ExitProcess(2); } // Generate unique data for each thread to work with. pDataArray[i]->val1 = i; pDataArray[i]->val2 = i+100; // Create the thread to begin execution on its own. hThreadArray[i] = CreateThread( NULL, // default security attributes 0, // use default stack size MyThreadFunction, // thread function name pDataArray[i], // argument to thread function 0, // use default creation flags &dwThreadIdArray[i]); // returns the thread identifier // Check the return value for success. // If CreateThread fails, terminate execution. // This will automatically clean up threads and memory. if (hThreadArray[i] == NULL) { ErrorHandler(TEXT("CreateThread")); ExitProcess(3); } } // End of main thread creation loop. // Wait until all threads have terminated. WaitForMultipleObjects(MAX_THREADS, hThreadArray, TRUE, INFINITE); // Close all thread handles and free memory allocations.

     

    Here is the related topic which might be helpful to solve your problem:

    UDF to update multiple cells

    Since your question is much related to OfficeDev,if it remains unresolved,please consider creating another thread on the related forum such as Excel for Developers.

    Best Regards.

    Jane.


    Jane Wang
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2013年8月8日 7:52
    版主