Executing VBA Macros using aspose.cells

Aspose.Cells Product Family
1

Hi,

I want to clear dependent dropdown content if parent dropdown value is changed in excel.
For this, I need to run VBA macro. Does Aspose.Cells support this feature? If not, is there any other alternative solution to the above problem.

2

@deergha,

Thanks for your query.

I am afraid, currently, Aspose.Cells does not support to execute/run vba codes or macro functions although we have supported to create, manipulate or embed VBA codes/ macros. We still however could not support to run or execute macros or vba code.

If you could perform your task in MS Excel manually (without using vba codes or macros), Aspose.Cells should do that.

Feel free to write us back if you have fuhrer comments or questions, we will be happy to assist you soon.

3

Hi @deergha , @Amjad_Sahi ,
I have used below code to clear dependent content if parent dropdown value is changed in excel.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

int moduleIdx = wb.VbaProject.Modules.Add(worksheet);
Aspose.Cells.Vba.VbaModule module = wb.VbaProject.Modules[moduleIdx];
module.Codes =

        "Sub Worksheet_Change(ByVal Target As Range)" + "\r\n" +
             "   If Target.Column = 2 And Target.Validation.Type = 3 Then" + "\r\n" +
                    " Target.Offset(0, 1).Value = \" \"" + "\r\n" +
                 "End If" + "\r\n" +
                
            "End Sub";

save workbook as xlsm…

It was working fine before, now sometimes i get below error:May i know any solution for this…
Run-time error ’ 1004 '. Application-defined or operation-defined error .

Thanks in advance !

4

@RenukaH,

I tested your code segment, it works Ok. Aspose.Cells embeds vba codes just fine in the output Excel file. Your issue does not concern to Aspose.Cells APIs by any means. Please browse relevant threads for reference on your issue. For example, see the following threads for your reference:

Hope, this helps a bit.

5

@Amjad_Sahi
Thanks but I already browsed for solution on this but no luck…
Actually I got clue like why this error is getting triggered but unable to have solution.

Cause:
When i try to enter values in Cells which do not have Dropdown enabled , the same causes error…PFA image…
I , J K has inter-dependent DD… and macros works file there…
if i try to enter value in cell A2/B2/C2 ; mentioned error causes…

Plz let me know u have come across such scenarion / what can be done in this case if you know .
image.png (23.0 KB)

Thanks in advance!

6

@RenukaH,

As we told you that Aspose.Cells has nothing to do with your mentioned error. Aspose.Cells can only embed or remove macros/vba codes in the workbook. I do not remember if we have come across such an error posted from users before. If you still think there is some issue with Aspose.Cells APIs regarding inserting or manipulating vba codes or macros, kindly do share a console application (runnable), zip the project and post us, we will check it soon. Also provide two outputs files, one is generated by Aspose.Cells APIs which demonstrates the issue and other should be created manually in MS Excel which should not demonstrate the issue (it should work fine). We will check your issue soon.

7

Hi @Amjad_Sahi,
Issue has been resolved.
I have changed logic of VBA code. Removed "And Target.Validation.Type = 3 " condition as it was throwing error while inserting values in other cell which do not have List type validation…

Thanks for all your help!!

8

@RenukaH,

Good to know that your issue is sorted out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

1 Like