Excel VBA - VSTO Speed Up Speed Down Functions

Sub SpeedUp()
       
    Application.EnableCancelKey = xlDisabled
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

End Sub

Sub SpeedDown()
   
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    Application.ScreenUpdating = True
   
    Application.EnableCancelKey = xlInterrupt
End Sub

VSTO - Performance - Set 1000000 value within 2 seconds

        public void RunTest(String columnName)
        {
            var start = DateTime.Now;

            var sheet = Globals.ThisWorkbook.Sheets.getByName("Sheet3");

            //set data area
            Excel.Range dataArea = sheet.Range(String.Format("{0}1:{0}1000000", columnName));
            //prepare dummy data
            Object[,] data = new Object[1000000,1];
            for (int i = 0; i < 1000000; i++)
            {
                data[i, 0] = i;
            }
            Debug.WriteLine("Data ready!!!");
            // set data
            dataArea.Value = data;

            var duration = (DateTime.Now - start).TotalMilliseconds;
            Debug.WriteLine(duration.ToString());
            MessageBox.Show(duration.ToString());
        }

vba automation error the object invoked has disconnected from its clients

vba automation error the object invoked has disconnected from its clients

if you encounter this error, just delete the worksheet and recreate it.
while copying and pasting data, use another instance of excel workbook and worksheet.

first copy data to another temp excel, 
create new worksheet
copy data from temp excel to new worksheet

Excel Environ Parameters

Sample

Environ("USERNAME")
Environ("USERDOMAIN")

---

Environ arguments

• ALLUSERSPROFILE
• APPDATA
• AVENGINE
• CLIENTNAME
• CommonProgramFiles
• COMPUTERNAME
• ComSpec
• FP_NO_HOST_CHECK
• HOMEDRIVE
• HOMEPATH
• INCLUDE
• INOCULAN
• LIB
• LOGONSERVER
• NUMBER_OF_PROCESSORS
• OS
• Path
• PATHEXT
• PROCESSOR_ARCHITECTURE
• PROCESSOR_IDENTIFIER
• PROCESSOR_LEVEL
• PROCESSOR_REVISION
• ProgramFiles
• SESSIONNAME
• SystemDrive
• SystemRoot
• TEMP
• TMP
• USERDOMAIN
• USERNAME
• USERPROFILE