Macro is NOT recording my steps when i use Data, data analysis, Covariance to calculate the Covr Mtx

[Hanya2002]

As per your request , I state my PC specs. I have desk top HP . installed windows 10 education version 22H2 , OS Build 19045.3086. installed MICROSOFT 365 application for enterprise.

I am re-building Excel model to produce for me the efficient frontier line and weights for 5 stocks selected ( 1st model I created was for 20 stocks) . The model uses a matrix to calculate the Covariance among the 5 stocks . 

I am using MACRO to run my model whenever the stocks selected changed or the input data changed.

My problem:

In my new 5 stocks model, I made a separate small Macro “MTX” to calculate the Covariance for the 5 stocks [ Sheet2, cell range E23 : i82] to test it before I copy/paste it in my large MACRO which is called “AutoSolver”.

This “MTX” Macro should work to fill specific matrix areas in Sheet # 2.

The MTX macro is NOT RECORDING THE STEPS I USED “Data, Data analysis , Covariance” at all ….. !!!!  I do not know why , SEEMS THERE IS A BUG in MS 365 version. As before this version (excel 365) , when I created this model for 20 stocks [some time ago] under perhaps Excel 2016, Covariance matrix calculation worked fine and still working fine. But now, I can not use Covariance application under Data analysis icon in the new macro “MTX” I am creating.

To overcome this obstacle, I tried to create a very small separate Macro “Analy” using the Covariance application only without any other steps and copy paste it in the MTX macro . It worked one time , then stopped in 2nd test. the “MTX” Macro again stopped and says Error at this step shown below ( line 19 in MTX macro) :

Application.Run "ATPVBAEN.XLAM!Mcovar", ActiveSheet.Range("$E$23:$I$82"), _

        ActiveSheet.Range("$D$157"), "C", False

Is there a solution to this problem?

n.b. The large main Macro “autosolver” is not yet amended in this file pending finding a solution to the matrix calculation. it is still working as if 20 stocks NOT 5 stocks .

I need to attach the Excel file but i do not know how ?

[#2]

@Hanya2002 

I understand that you are encountering issues with recording the steps for the "Data Analysis" tool in Excel 365 and experiencing errors in your macro. It is possible that there might be compatibility or configuration issues causing the problem. Here are a few suggestions to help resolve the issue:

1. Enable the "Data Analysis" Tool: The "Data Analysis" tool may not be enabled by default in Excel. You can check if it is enabled by going to "File" > "Options" > "Add-Ins." In the "Manage" drop-down list at the bottom, select "Excel Add-Ins" and click "Go." Ensure that "Analysis ToolPak" is checked, then click "OK." If it was not checked, enabling it should make the "Data Analysis" tool available.
2. Check Macro Security Settings: Verify that your macro security settings are not blocking the execution of macros. Go to "File" > "Options" > "Trust Center" > "Trust Center Settings" > "Macro Settings." Select the option that enables macros, such as "Enable all macros." However, exercise caution when enabling macros from untrusted sources.
3. Use VBA Functions Instead: Instead of relying on the "Data Analysis" tool, you can use VBA functions to calculate the covariance matrix directly in your macro. Here's an example of how you can calculate the covariance matrix using VBA:

Vba code:

Sub CalculateCovarianceMatrix()
    Dim rngData As Range
    Dim rngOutput As Range
    
    ' Specify the input data range
    Set rngData = ThisWorkbook.Sheets("Sheet2").Range("E23:I82")
    
    ' Specify the output range for the covariance matrix
    Set rngOutput = ThisWorkbook.Sheets("Sheet2").Range("D157")
    
    ' Calculate the covariance matrix using the COVAR function
    rngOutput.FormulaArray = "=COVAR($E$23:$I$82)"
End Sub

1. Debug and Troubleshoot: If the error persists, you can try debugging your macro to identify the specific issue. Place a breakpoint at the problematic line (line 19 in your "MTX" macro) and step through the code using the debugger to see if any errors or unexpected behavior occur. This can provide more information about the cause of the error.

Regarding attaching the Excel file, unfortunately, file attachments are not supported in this text-based interface. However, you can upload the file to a file-sharing service (e.g., Google Drive, Dropbox) and share the download link here so that I can take a closer look at your macro code if needed. Since no one has answered it, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.