Sub Macro1()

   Range("E2:E13").Select

   With Selection.Validation

       .Delete

       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

       xlBetween, Formula1:="=$I$1:$I$5"

       .IgnoreBlank = True

       .InCellDropdown = True

       .InputTitle = ""

       .ErrorTitle = ""

       .InputMessage = ""

       .ErrorMessage = ""

       .IMEMode = xlIMEModeNoControl

       .ShowInput = True

       .ShowError = True

   End With

End Sub

// 项目 -> 添加引用 ->COM->Microsoft Excel XX.X Object Library

using Excel = Microsoft . Office . Interop . Excel;

using Microsoft . Office . Interop . Excel;

using Microsoft . Office . Core;

using Microsoft . Vbe . Interop;

Excel . Application vExcel = new Excel . Application ();

vExcel . Workbooks . Add( true );

vExcel . Visible = true ;

Worksheet vWorksheet = ( Worksheet )vExcel . Worksheets[ 1 ];

Range vRange = vWorksheet . get_Range( "E2" , "E13" );

vRange . Select();

vRange . Validation . Delete();

vRange . Validation . Add( XlDVType . xlValidateList,

    XlDVAlertStyle . xlValidAlertStop,

    XlFormatConditionOperator . xlBetween, "=$I$1:$I$5" , null );

vRange . Validation . IgnoreBlank = true ;

vRange . Validation . InCellDropdown = true ;

vRange . Validation . InputTitle = "" ;

vRange . Validation . ErrorTitle = "" ;

vRange . Validation . InputMessage = "" ;

vRange . Validation . ErrorMessage = "" ;

vRange . Validation . IMEMode = ( int ) XlIMEMode . xlIMEModeNoControl;

vRange . Validation . ShowInput = true ;

vRange . Validation . ShowError = true ;

//"Basic Project "

//

//Basic

CodeModule vCodeModule = vExcel . ActiveWorkbook . VBProject . VBComponents . Add(

    vbext_ComponentType . vbext_ct_StdModule) . CodeModule;

vCodeModule . AddFromString(

    @"Sub MyMacro()" + "\r\n" +

    @"    With Range(""E2:E13"").Validation" + "\r\n" +

    @"        .Delete" + "\r\n" +

    @"        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _" + "\r\n" +

    @"        xlBetween, Formula1:=""=$I$1:$I$5""" + "\r\n" +

    @"        .IgnoreBlank = True" + "\r\n" +

    @"        .InCellDropdown = True" + "\r\n" +

    @"        .InputTitle = """"" + "\r\n" +

    @"        .ErrorTitle = """"" + "\r\n" +

    @"        .InputMessage = """"" + "\r\n" +

    @"        .ErrorMessage = """"" + "\r\n" +

    @"        .IMEMode = xlIMEModeNoControl" + "\r\n" +

    @"        .ShowInput = True" + "\r\n" +

    @"        .ShowError = True" + "\r\n" +

    @"    End With" + "\r\n" +

    @"End Sub" + "\r\n" );

vExcel . Run( "MyMacro()" , null , null , null , null , null , null , null , null , null , null ,

    null , null , null , null , null , null , null , null , null , null ,

    null , null , null , null , null , null , null , null , null , null );