Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
Is there a commonly-accepted method for creating VBA functions in a
spreadsheet that one is creating via VBA (in this case from an MS Access routine)? What I'm doing now is defining a monster constant in MS Access VBA that represents an entire EXCEL VBA routine and then copying that constant into the newly-created Excel spreadsheet with some on-the-fly modifications to the constant values. Just got a request to add some what-if functionality to a spreadsheet that I'm already creating and I figured it was time to make sure I'm doing it using some sort of "best practice". Here's an example of some MS Access code I'm currently using. It lets the user click on a column header and have the sheet sorted on that columns values. Probably looks pretty bad when rendered in a variable-pitch font... will line up Ok using Courier New. Is my basic approach (i.e. the monster constant "myMacroCode02") valid? Or is there a better way? -------------------------------------------------------------------------------------------------- Public Sub SortButtons_Create(ByVal theRowNum_Buttons As Long, ByVal theRowNum_DataFirst As Long, ByVal theRowNum_DataLast As Long, ByVal theColNum_ButtonFirst As Long, ByVal theColNum_ButtonLast As Long, ByVal theColNum_DataFirst As Long, ByVal theColNum_DataLast As Long, ByVal theArrowColor As Long, ByRef theWS As Excel.Worksheet, Optional theMacroName As String, Optional theColNum_SubTotals As Long, Optional theSubTotals_Label As String) 13000 debugStackPush mModuleName & ": SortButtons_Create" 13001 On Error GoTo SortButtons_Create_err ' PURPOSE: - To put a series of invisible rectangles on a worksheet which, when clicked, ' call a routine that sorts the entire sheet's data on that column's values. ' - To create up/down arrows to supplement the rectangles by servint as visual indicator ' of what is sorted on and how ' - To create/install a macro named "SortSheet" that will serve as the routine that sorts the sheet ' ACCEPTS: - Row number of the row to have the invisible rectangles installed on it ' - Row number of the first row tb sorted ' - Row number of the last row tb sorted ' - Col number of first column that gets a button ' - Col number of last column that gets a button ' - Col number of first column tb sorted (generally same as first col to get a button) ' - Col number of last column tb sorted (generally same as last col to get a button) ' - Color tb used when drawing the Up/Down arrows. Must be valid in Excel's scheme of things. ' e.g. 10 = Red ' - Pointer to the Excel.Worksheet where the buttons go ' - OPTIONAL name of sort macro name. TB used if/when we need to install multiple ' macros in a single sheet - as with Market Value Changes report, which has a ' separate macro for each report grouping. ' - OPTIONAL column number of label that identifies subtotal lines (so they can be removed and ' not confuse the sort result) ' - OPTIONAL literal value of subtotal lines' label ' ' NOTES: 1) BEWARE OF Null CELLS. If a column on the sheet tb sorted contains Null values, ' the Sort command will break down and the user will not be able to flip-flop ' the direction. SO: For strings and dates, if the cell were tb Null, you ' need to populate it with a space. For numerics, it must be populated with ' zero. Let the formatting hide the zeros if the user doesn't want to see them. 13002 Dim myWB As Excel.Workbook Dim myRange As Excel.Range Dim curCell As Excel.Range Dim curButton As Shape Dim curUpArrow As Shape Dim curDownArrow As Shape Dim myParentModule As VBComponent Dim myCodeModule As CodeModule Dim curRI As RangeInfo Dim curCellAddress As String Dim curColNumString As String Dim myMacroCode As String Dim myMacroName As String Dim okToProceed As Boolean Const myArrowHeight As Long = 5 Const myArrowWidth As Long = 5 Const myDefaultMacroName As String = "SortSheet" 'This value is implicit in myMacroCode1 ' ----------------------------------------------------------- ' We use these constants to assemble the macro tb added to the SS ' which does the actual sorting Const myMacroCode01 As String = "Sub " ' concat myMacroName Const myMacroCode02 As String = _ "() " & vbCrLf & vbCrLf & _ "'PURPOSE: - To allow user to sort the entire sheet by clicking on a column header" & vbCrLf & _ "' - To maintain visibility of up/down arrows which indicate which cols are sorted and" & vbCrLf & _ "' the direction of the sort" & vbCrLf & _ "'" & vbCrLf & _ "' NOTES: 1) This routine's code was generated by the same application (""CDO"")" & vbCrLf & _ "' that created this spreadsheet. That is why the data area's dimensions" & vbCrLf & _ "' are supplied via constants: the creating app concatonated them into this code" & vbCrLf & _ "' Pete Cresswell" & vbCrLf & _ "' 610-513-0066" & vbCrLf & _ " Dim myWS As Worksheet " & vbCrLf & _ " Dim myRange As Range " & vbCrLf & vbCrLf & _ " Dim i As Long " & vbCrLf & _ " Dim R As Long " & vbCrLf & _ " Dim mySortCol As Long " & vbCrLf & _ " Dim mySortOrder As Long " & vbCrLf & _ " Dim myWeight As Long " & vbCrLf & _ " Dim myLineStyle As Long " & vbCrLf & _ " Dim myCallerName As string " & vbCrLf & vbCrLf & _ " Static subTotals_Removed As Boolean " & vbCrLf & _ " Static rowNum_LastData_Revised As Long " & vbCrLf & vbCrLf & _ " Const rowNum_FirstData As Long = " ' concat theRowNum_DataFirst Const myMacroCode03 As String = " Const rowNum_LastData_Begin As Long = " Const myMacroCode04 As String = " Const colNum_FirstData As Long = " Const myMacroCode05 As String = " Const colNum_LastData As Long = " Const myMacroCode06 As String = " Const colNum_SubTotals As Long = " Const myMacroCode07 As String = " Const subTotals_Label As String = """ Const myMacroCode08 As String = _ " Set myWS = ActiveSheet " & vbCrLf & vbCrLf & _ "' ---------------------------------------------------------" & vbCrLf & _ "' If creating routine has specified subtotals exist," & vbCrLf & _ "' empty out entire line for each subtotal" & vbCrLf & _ "' " & vbCrLf & _ "' We *could* delete those lines, but that would introduce" & vbCrLf & _ "' the complexities of adjusting boxes around data and we" & vbCrLf & _ "' don't want to go there..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " If subTotals_Removed = False Then " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Begin " & vbCrLf & _ " If ((Len(subTotals_Label & """") 0) And (colNum_SubTotals 0)) Then " & vbCrLf & _ "' First take care of last row if it is a subtotal, preserving any bold bottom border" & vbCrLf & _ " R = rowNum_LastData_Revised " & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " myWeight = .Weight" & vbCrLf & _ " myLineStyle = .LineStyle" & vbCrLf & _ " End With" Const myMacroCode09 As String = _ " .Rows(rowNum_LastData_Revised).Delete" & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1" & vbCrLf & _ " R = rowNum_LastData_Revised" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " .Weight = myWeight" & vbCrLf & _ " .LineStyle = myLineStyle" & vbCrLf & _ " End With" & vbCrLf & _ " End If" & vbCrLf & vbCrLf & _ "' Now deal with all the other subtotals " & vbCrLf & _ " For R = rowNum_FirstData To rowNum_LastData_Begin" & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then " & vbCrLf & _ " .Rows(R).Delete Shift:=xlDown " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1 " & vbCrLf & _ " End If " & vbCrLf & _ " Next R " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " Else " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " End If" & vbCrLf & _ " End If" & vbCrLf & _ " End With " & vbCrLf & vbCrLf Const myMacroCode10 As String = _ "' --------------------------------------------------------- " & vbCrLf & _ "' Do the sorting thing..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " myCallerName = .Shapes(Application.Caller).Name" & vbCrLf & vbCrLf & _ " For i = colNum_FirstData To colNum_LastData" & vbCrLf & _ " On Error Resume Next 'User may have deleted 1 or more columns" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode11 As String = _ """ & Format$(i, ""000"") & ""Up"").Visible = False" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode12 As String = _ """ & Format$(i, ""000"") & ""Dn"").Visible = False" & vbCrLf Const myMacroCode13 As String = _ " On Error GoTo 0 " & vbCrLf & _ " Next i" & vbCrLf & vbCrLf & _ " mySortCol = .Shapes(Application.Caller).TopLeftCell.Column " & vbCrLf & _ " Set myRange = .Range(.Cells(rowNum_FirstData, colNum_FirstData), .Cells(rowNum_LastData_Revised, colNum_LastData)) " & vbCrLf & vbCrLf Const myMacroCode14 As String = _ " If .Cells(rowNum_FirstData, mySortCol).Value < .Cells(rowNum_LastData_Revised, mySortCol).Value Then " & vbCrLf & _ " mySortOrder = xlDescending " & vbCrLf & _ " On Error Resume Next 'User may have deleted one or more arrows " & vbCrLf & _ " .Shapes(myCallerName & ""Dn"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " Else " & vbCrLf & _ " mySortOrder = xlAscending " & vbCrLf & _ " On Error Resume Next" & vbCrLf & _ " .Shapes(myCallerName & ""Up"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " End If " & vbCrLf & vbCrLf & _ " myRange.Sort key1:=.Cells(rowNum_FirstData, mySortCol), order1:=mySortOrder " & vbCrLf & _ " End With " & vbCrLf & _ " End Sub " ' ------------------------------------------------------------------------ ' If there are not multiple data rows specified or if there are no data columns ' specified, call the whole thing off. 13010 If Abs((theRowNum_DataLast - theRowNum_DataFirst)) 0 Then 13011 If Abs((theColNum_ButtonLast - theColNum_ButtonFirst)) 0 Then 13012 okToProceed = True 13013 End If 13019 End If 13020 If okToProceed = True Then ' ------------------------------------------------------------------------ ' Check to see if we have a special macro name 13030 If Len(theMacroName & "") = 0 Then 13031 myMacroName = myDefaultMacroName 13032 Else 13033 myMacroName = theMacroName 13039 End If ' ------------------------------------------------------------------------ ' Create a code module in the target spreadsheet ' that will hold the code to handle our button click events 13040 Set myWB = theWS.Parent 13041 Set myParentModule = myWB.VBProject.VBComponents.Add(vbext_ct_StdModule ) 13049 Set myCodeModule = myParentModule.CodeModule 13050 myMacroCode = myMacroCode01 & myMacroName & _ myMacroCode02 & theRowNum_DataFirst & vbCrLf & _ myMacroCode03 & theRowNum_DataLast & vbCrLf & _ myMacroCode04 & theColNum_ButtonFirst & vbCrLf & _ myMacroCode05 & theColNum_DataLast & vbCrLf & _ myMacroCode06 & Val(theColNum_SubTotals & "") & vbCrLf & _ myMacroCode07 & theSubTotals_Label & Chr$(34) & vbCrLf & _ myMacroCode08 & vbCrLf & _ myMacroCode09 & _ myMacroCode10 & myMacroName & _ myMacroCode11 & myMacroName & _ myMacroCode12 & _ myMacroCode13 & _ myMacroCode14 13060 With myCodeModule 13061 .InsertLines .CountOfLines + 1, myMacroCode 13069 End With ' ------------------------------------------------------------------------ ' Now that we've got our macro code installed in the target Excel workbook, ' we loop through the worksheet's columns, creating a rectangle/ button ' and a couple of directional indicator arrows in each column header cell ' NB: If the text in a column header is right-justified, you'll need to ' have done a .IndentLevel=1 to slide it over far enough so the Up/Down ' arrows do not conflict with it 13070 With theWS 13071 Set myRange = .Range(.Cells(theRowNum_Buttons, theColNum_ButtonFirst), .Cells(theRowNum_Buttons, theColNum_ButtonLast)) 13079 For Each curCell In myRange.Cells 13080 With curCell 13081 curCellAddress = .Address(ReferenceStyle:=xlR1C1) 13084 Set curButton = .Parent.Shapes.AddShape(Type:=msoShapeRectangle, Top:=.Top, Height:=.Height, Width:=.Width, Left:=.Left) 13085 Set curUpArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13086 Set curDownArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13089 End With 13090 curRI = RangeAddress_Parse(curCellAddress) 13099 curColNumString = Format$(curRI.ColLeft, "000") 13110 With curButton 13111 .Name = myMacroName & curColNumString 13112 .OnAction = myMacroName 13113 .Fill.Visible = msoFalse 13114 .Line.Visible = msoFalse 13115 .Placement = xlMoveAndSize 13119 End With 13120 With curUpArrow 13101 .Name = myMacroName & curColNumString & "Up" 13122 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13129 .Placement = xlMove 13130 With .Fill 13131 .Solid 13132 .ForeColor.SchemeColor = theArrowColor 13139 End With 13199 End With 13200 With curDownArrow 13201 .Name = myMacroName & curColNumString & "Dn" 13202 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13203 .Placement = xlMove 13209 .IncrementRotation 180 13211 With .Fill 13212 .Solid 13213 .ForeColor.SchemeColor = theArrowColor 13219 End With 13299 End With 13990 Next curCell 13991 End With 13999 End If SortButtons_Create_xit: DebugStackPop On Error Resume Next Set myRange = Nothing Set curCell = Nothing Set curButton = Nothing Set curDownArrow = Nothing Set curUpArrow = Nothing Set myParentModule = Nothing Set myCodeModule = Nothing Set myWB = Nothing Exit Sub SortButtons_Create_err: BugAlert True, "" Resume SortButtons_Create_xit End Sub -------------------------------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
Oops!
Just reviewed the OP and the example VBA code still looks like a dog's breakfast - even with Courier New. Seems like somebody's doing some text wrapping somewhere along the way. If anybody wants to see the original code, flip me an email at [firstname] dot [lastname] at FatBelly fullstop Com. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
Per PeteCresswell:
BA code still looks like a dog's breakfast I uploaded a .txt file to http://tinyurl.com/2smt2v -- PeteCresswell |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
Stick the function into a pre-formatted Excel workbook, which has been saved
as a template. When creating a new workbook, simply base it on this template, and save yourself a ream of code. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "PeteCresswell" wrote in message ups.com... Is there a commonly-accepted method for creating VBA functions in a spreadsheet that one is creating via VBA (in this case from an MS Access routine)? What I'm doing now is defining a monster constant in MS Access VBA that represents an entire EXCEL VBA routine and then copying that constant into the newly-created Excel spreadsheet with some on-the-fly modifications to the constant values. Just got a request to add some what-if functionality to a spreadsheet that I'm already creating and I figured it was time to make sure I'm doing it using some sort of "best practice". Here's an example of some MS Access code I'm currently using. It lets the user click on a column header and have the sheet sorted on that columns values. Probably looks pretty bad when rendered in a variable-pitch font... will line up Ok using Courier New. Is my basic approach (i.e. the monster constant "myMacroCode02") valid? Or is there a better way? -------------------------------------------------------------------------------------------------- Public Sub SortButtons_Create(ByVal theRowNum_Buttons As Long, ByVal theRowNum_DataFirst As Long, ByVal theRowNum_DataLast As Long, ByVal theColNum_ButtonFirst As Long, ByVal theColNum_ButtonLast As Long, ByVal theColNum_DataFirst As Long, ByVal theColNum_DataLast As Long, ByVal theArrowColor As Long, ByRef theWS As Excel.Worksheet, Optional theMacroName As String, Optional theColNum_SubTotals As Long, Optional theSubTotals_Label As String) 13000 debugStackPush mModuleName & ": SortButtons_Create" 13001 On Error GoTo SortButtons_Create_err ' PURPOSE: - To put a series of invisible rectangles on a worksheet which, when clicked, ' call a routine that sorts the entire sheet's data on that column's values. ' - To create up/down arrows to supplement the rectangles by servint as visual indicator ' of what is sorted on and how ' - To create/install a macro named "SortSheet" that will serve as the routine that sorts the sheet ' ACCEPTS: - Row number of the row to have the invisible rectangles installed on it ' - Row number of the first row tb sorted ' - Row number of the last row tb sorted ' - Col number of first column that gets a button ' - Col number of last column that gets a button ' - Col number of first column tb sorted (generally same as first col to get a button) ' - Col number of last column tb sorted (generally same as last col to get a button) ' - Color tb used when drawing the Up/Down arrows. Must be valid in Excel's scheme of things. ' e.g. 10 = Red ' - Pointer to the Excel.Worksheet where the buttons go ' - OPTIONAL name of sort macro name. TB used if/when we need to install multiple ' macros in a single sheet - as with Market Value Changes report, which has a ' separate macro for each report grouping. ' - OPTIONAL column number of label that identifies subtotal lines (so they can be removed and ' not confuse the sort result) ' - OPTIONAL literal value of subtotal lines' label ' ' NOTES: 1) BEWARE OF Null CELLS. If a column on the sheet tb sorted contains Null values, ' the Sort command will break down and the user will not be able to flip-flop ' the direction. SO: For strings and dates, if the cell were tb Null, you ' need to populate it with a space. For numerics, it must be populated with ' zero. Let the formatting hide the zeros if the user doesn't want to see them. 13002 Dim myWB As Excel.Workbook Dim myRange As Excel.Range Dim curCell As Excel.Range Dim curButton As Shape Dim curUpArrow As Shape Dim curDownArrow As Shape Dim myParentModule As VBComponent Dim myCodeModule As CodeModule Dim curRI As RangeInfo Dim curCellAddress As String Dim curColNumString As String Dim myMacroCode As String Dim myMacroName As String Dim okToProceed As Boolean Const myArrowHeight As Long = 5 Const myArrowWidth As Long = 5 Const myDefaultMacroName As String = "SortSheet" 'This value is implicit in myMacroCode1 ' ----------------------------------------------------------- ' We use these constants to assemble the macro tb added to the SS ' which does the actual sorting Const myMacroCode01 As String = "Sub " ' concat myMacroName Const myMacroCode02 As String = _ "() " & vbCrLf & vbCrLf & _ "'PURPOSE: - To allow user to sort the entire sheet by clicking on a column header" & vbCrLf & _ "' - To maintain visibility of up/down arrows which indicate which cols are sorted and" & vbCrLf & _ "' the direction of the sort" & vbCrLf & _ "'" & vbCrLf & _ "' NOTES: 1) This routine's code was generated by the same application (""CDO"")" & vbCrLf & _ "' that created this spreadsheet. That is why the data area's dimensions" & vbCrLf & _ "' are supplied via constants: the creating app concatonated them into this code" & vbCrLf & _ "' Pete Cresswell" & vbCrLf & _ "' 610-513-0066" & vbCrLf & _ " Dim myWS As Worksheet " & vbCrLf & _ " Dim myRange As Range " & vbCrLf & vbCrLf & _ " Dim i As Long " & vbCrLf & _ " Dim R As Long " & vbCrLf & _ " Dim mySortCol As Long " & vbCrLf & _ " Dim mySortOrder As Long " & vbCrLf & _ " Dim myWeight As Long " & vbCrLf & _ " Dim myLineStyle As Long " & vbCrLf & _ " Dim myCallerName As string " & vbCrLf & vbCrLf & _ " Static subTotals_Removed As Boolean " & vbCrLf & _ " Static rowNum_LastData_Revised As Long " & vbCrLf & vbCrLf & _ " Const rowNum_FirstData As Long = " ' concat theRowNum_DataFirst Const myMacroCode03 As String = " Const rowNum_LastData_Begin As Long = " Const myMacroCode04 As String = " Const colNum_FirstData As Long = " Const myMacroCode05 As String = " Const colNum_LastData As Long = " Const myMacroCode06 As String = " Const colNum_SubTotals As Long = " Const myMacroCode07 As String = " Const subTotals_Label As String = """ Const myMacroCode08 As String = _ " Set myWS = ActiveSheet " & vbCrLf & vbCrLf & _ "' ---------------------------------------------------------" & vbCrLf & _ "' If creating routine has specified subtotals exist," & vbCrLf & _ "' empty out entire line for each subtotal" & vbCrLf & _ "' " & vbCrLf & _ "' We *could* delete those lines, but that would introduce" & vbCrLf & _ "' the complexities of adjusting boxes around data and we" & vbCrLf & _ "' don't want to go there..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " If subTotals_Removed = False Then " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Begin " & vbCrLf & _ " If ((Len(subTotals_Label & """") 0) And (colNum_SubTotals 0)) Then " & vbCrLf & _ "' First take care of last row if it is a subtotal, preserving any bold bottom border" & vbCrLf & _ " R = rowNum_LastData_Revised " & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " myWeight = .Weight" & vbCrLf & _ " myLineStyle = .LineStyle" & vbCrLf & _ " End With" Const myMacroCode09 As String = _ " .Rows(rowNum_LastData_Revised).Delete" & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1" & vbCrLf & _ " R = rowNum_LastData_Revised" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " .Weight = myWeight" & vbCrLf & _ " .LineStyle = myLineStyle" & vbCrLf & _ " End With" & vbCrLf & _ " End If" & vbCrLf & vbCrLf & _ "' Now deal with all the other subtotals " & vbCrLf & _ " For R = rowNum_FirstData To rowNum_LastData_Begin" & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then " & vbCrLf & _ " .Rows(R).Delete Shift:=xlDown " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1 " & vbCrLf & _ " End If " & vbCrLf & _ " Next R " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " Else " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " End If" & vbCrLf & _ " End If" & vbCrLf & _ " End With " & vbCrLf & vbCrLf Const myMacroCode10 As String = _ "' --------------------------------------------------------- " & vbCrLf & _ "' Do the sorting thing..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " myCallerName = .Shapes(Application.Caller).Name" & vbCrLf & vbCrLf & _ " For i = colNum_FirstData To colNum_LastData" & vbCrLf & _ " On Error Resume Next 'User may have deleted 1 or more columns" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode11 As String = _ """ & Format$(i, ""000"") & ""Up"").Visible = False" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode12 As String = _ """ & Format$(i, ""000"") & ""Dn"").Visible = False" & vbCrLf Const myMacroCode13 As String = _ " On Error GoTo 0 " & vbCrLf & _ " Next i" & vbCrLf & vbCrLf & _ " mySortCol = .Shapes(Application.Caller).TopLeftCell.Column " & vbCrLf & _ " Set myRange = .Range(.Cells(rowNum_FirstData, colNum_FirstData), .Cells(rowNum_LastData_Revised, colNum_LastData)) " & vbCrLf & vbCrLf Const myMacroCode14 As String = _ " If .Cells(rowNum_FirstData, mySortCol).Value < .Cells(rowNum_LastData_Revised, mySortCol).Value Then " & vbCrLf & _ " mySortOrder = xlDescending " & vbCrLf & _ " On Error Resume Next 'User may have deleted one or more arrows " & vbCrLf & _ " .Shapes(myCallerName & ""Dn"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " Else " & vbCrLf & _ " mySortOrder = xlAscending " & vbCrLf & _ " On Error Resume Next" & vbCrLf & _ " .Shapes(myCallerName & ""Up"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " End If " & vbCrLf & vbCrLf & _ " myRange.Sort key1:=.Cells(rowNum_FirstData, mySortCol), order1:=mySortOrder " & vbCrLf & _ " End With " & vbCrLf & _ " End Sub " ' ------------------------------------------------------------------------ ' If there are not multiple data rows specified or if there are no data columns ' specified, call the whole thing off. 13010 If Abs((theRowNum_DataLast - theRowNum_DataFirst)) 0 Then 13011 If Abs((theColNum_ButtonLast - theColNum_ButtonFirst)) 0 Then 13012 okToProceed = True 13013 End If 13019 End If 13020 If okToProceed = True Then ' ------------------------------------------------------------------------ ' Check to see if we have a special macro name 13030 If Len(theMacroName & "") = 0 Then 13031 myMacroName = myDefaultMacroName 13032 Else 13033 myMacroName = theMacroName 13039 End If ' ------------------------------------------------------------------------ ' Create a code module in the target spreadsheet ' that will hold the code to handle our button click events 13040 Set myWB = theWS.Parent 13041 Set myParentModule = myWB.VBProject.VBComponents.Add(vbext_ct_StdModule ) 13049 Set myCodeModule = myParentModule.CodeModule 13050 myMacroCode = myMacroCode01 & myMacroName & _ myMacroCode02 & theRowNum_DataFirst & vbCrLf & _ myMacroCode03 & theRowNum_DataLast & vbCrLf & _ myMacroCode04 & theColNum_ButtonFirst & vbCrLf & _ myMacroCode05 & theColNum_DataLast & vbCrLf & _ myMacroCode06 & Val(theColNum_SubTotals & "") & vbCrLf & _ myMacroCode07 & theSubTotals_Label & Chr$(34) & vbCrLf & _ myMacroCode08 & vbCrLf & _ myMacroCode09 & _ myMacroCode10 & myMacroName & _ myMacroCode11 & myMacroName & _ myMacroCode12 & _ myMacroCode13 & _ myMacroCode14 13060 With myCodeModule 13061 .InsertLines .CountOfLines + 1, myMacroCode 13069 End With ' ------------------------------------------------------------------------ ' Now that we've got our macro code installed in the target Excel workbook, ' we loop through the worksheet's columns, creating a rectangle/ button ' and a couple of directional indicator arrows in each column header cell ' NB: If the text in a column header is right-justified, you'll need to ' have done a .IndentLevel=1 to slide it over far enough so the Up/Down ' arrows do not conflict with it 13070 With theWS 13071 Set myRange = .Range(.Cells(theRowNum_Buttons, theColNum_ButtonFirst), .Cells(theRowNum_Buttons, theColNum_ButtonLast)) 13079 For Each curCell In myRange.Cells 13080 With curCell 13081 curCellAddress = .Address(ReferenceStyle:=xlR1C1) 13084 Set curButton = .Parent.Shapes.AddShape(Type:=msoShapeRectangle, Top:=.Top, Height:=.Height, Width:=.Width, Left:=.Left) 13085 Set curUpArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13086 Set curDownArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13089 End With 13090 curRI = RangeAddress_Parse(curCellAddress) 13099 curColNumString = Format$(curRI.ColLeft, "000") 13110 With curButton 13111 .Name = myMacroName & curColNumString 13112 .OnAction = myMacroName 13113 .Fill.Visible = msoFalse 13114 .Line.Visible = msoFalse 13115 .Placement = xlMoveAndSize 13119 End With 13120 With curUpArrow 13101 .Name = myMacroName & curColNumString & "Up" 13122 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13129 .Placement = xlMove 13130 With .Fill 13131 .Solid 13132 .ForeColor.SchemeColor = theArrowColor 13139 End With 13199 End With 13200 With curDownArrow 13201 .Name = myMacroName & curColNumString & "Dn" 13202 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13203 .Placement = xlMove 13209 .IncrementRotation 180 13211 With .Fill 13212 .Solid 13213 .ForeColor.SchemeColor = theArrowColor 13219 End With 13299 End With 13990 Next curCell 13991 End With 13999 End If SortButtons_Create_xit: DebugStackPop On Error Resume Next Set myRange = Nothing Set curCell = Nothing Set curButton = Nothing Set curDownArrow = Nothing Set curUpArrow = Nothing Set myParentModule = Nothing Set myCodeModule = Nothing Set myWB = Nothing Exit Sub SortButtons_Create_err: BugAlert True, "" Resume SortButtons_Create_xit End Sub -------------------------------------------------------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
The better way, IMO, would be store your your VBA in a text file
(.bas), and then dynamically load the Module when it's needed. Something like: Dim objXL,objwb,oVBC,M Set objXL = CreateObject("Excel.Application") objXL.visible = true objXL.StatusBar = "Loading Module" objXL.DisplayAlerts = false Set objwb = objXL.Workbooks.Add Set oVBC = objwb.VBProject.VBComponents Set M = oVBC.Import(GetPath() & "\MyModule.bas") 'Do your own implementation of GetPath() objXL.StatusBar = "Running MyMacro" oCVX.Application.Run "MyModule.MyMacro" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
I agree with Jon.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating VBA Functions When Creating Spreadsheet Via VBA?
This technique will also save hours when the template is reformatted or
rearranged or the code is altered. It's way easier to apply changes to the actual template than to find, e.g., the line of code that sets font size to 10 and change it to 11, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Stick the function into a pre-formatted Excel workbook, which has been saved as a template. When creating a new workbook, simply base it on this template, and save yourself a ream of code. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "PeteCresswell" wrote in message ups.com... Is there a commonly-accepted method for creating VBA functions in a spreadsheet that one is creating via VBA (in this case from an MS Access routine)? What I'm doing now is defining a monster constant in MS Access VBA that represents an entire EXCEL VBA routine and then copying that constant into the newly-created Excel spreadsheet with some on-the-fly modifications to the constant values. Just got a request to add some what-if functionality to a spreadsheet that I'm already creating and I figured it was time to make sure I'm doing it using some sort of "best practice". Here's an example of some MS Access code I'm currently using. It lets the user click on a column header and have the sheet sorted on that columns values. Probably looks pretty bad when rendered in a variable-pitch font... will line up Ok using Courier New. Is my basic approach (i.e. the monster constant "myMacroCode02") valid? Or is there a better way? -------------------------------------------------------------------------------------------------- Public Sub SortButtons_Create(ByVal theRowNum_Buttons As Long, ByVal theRowNum_DataFirst As Long, ByVal theRowNum_DataLast As Long, ByVal theColNum_ButtonFirst As Long, ByVal theColNum_ButtonLast As Long, ByVal theColNum_DataFirst As Long, ByVal theColNum_DataLast As Long, ByVal theArrowColor As Long, ByRef theWS As Excel.Worksheet, Optional theMacroName As String, Optional theColNum_SubTotals As Long, Optional theSubTotals_Label As String) 13000 debugStackPush mModuleName & ": SortButtons_Create" 13001 On Error GoTo SortButtons_Create_err ' PURPOSE: - To put a series of invisible rectangles on a worksheet which, when clicked, ' call a routine that sorts the entire sheet's data on that column's values. ' - To create up/down arrows to supplement the rectangles by servint as visual indicator ' of what is sorted on and how ' - To create/install a macro named "SortSheet" that will serve as the routine that sorts the sheet ' ACCEPTS: - Row number of the row to have the invisible rectangles installed on it ' - Row number of the first row tb sorted ' - Row number of the last row tb sorted ' - Col number of first column that gets a button ' - Col number of last column that gets a button ' - Col number of first column tb sorted (generally same as first col to get a button) ' - Col number of last column tb sorted (generally same as last col to get a button) ' - Color tb used when drawing the Up/Down arrows. Must be valid in Excel's scheme of things. ' e.g. 10 = Red ' - Pointer to the Excel.Worksheet where the buttons go ' - OPTIONAL name of sort macro name. TB used if/when we need to install multiple ' macros in a single sheet - as with Market Value Changes report, which has a ' separate macro for each report grouping. ' - OPTIONAL column number of label that identifies subtotal lines (so they can be removed and ' not confuse the sort result) ' - OPTIONAL literal value of subtotal lines' label ' ' NOTES: 1) BEWARE OF Null CELLS. If a column on the sheet tb sorted contains Null values, ' the Sort command will break down and the user will not be able to flip-flop ' the direction. SO: For strings and dates, if the cell were tb Null, you ' need to populate it with a space. For numerics, it must be populated with ' zero. Let the formatting hide the zeros if the user doesn't want to see them. 13002 Dim myWB As Excel.Workbook Dim myRange As Excel.Range Dim curCell As Excel.Range Dim curButton As Shape Dim curUpArrow As Shape Dim curDownArrow As Shape Dim myParentModule As VBComponent Dim myCodeModule As CodeModule Dim curRI As RangeInfo Dim curCellAddress As String Dim curColNumString As String Dim myMacroCode As String Dim myMacroName As String Dim okToProceed As Boolean Const myArrowHeight As Long = 5 Const myArrowWidth As Long = 5 Const myDefaultMacroName As String = "SortSheet" 'This value is implicit in myMacroCode1 ' ----------------------------------------------------------- ' We use these constants to assemble the macro tb added to the SS ' which does the actual sorting Const myMacroCode01 As String = "Sub " ' concat myMacroName Const myMacroCode02 As String = _ "() " & vbCrLf & vbCrLf & _ "'PURPOSE: - To allow user to sort the entire sheet by clicking on a column header" & vbCrLf & _ "' - To maintain visibility of up/down arrows which indicate which cols are sorted and" & vbCrLf & _ "' the direction of the sort" & vbCrLf & _ "'" & vbCrLf & _ "' NOTES: 1) This routine's code was generated by the same application (""CDO"")" & vbCrLf & _ "' that created this spreadsheet. That is why the data area's dimensions" & vbCrLf & _ "' are supplied via constants: the creating app concatonated them into this code" & vbCrLf & _ "' Pete Cresswell" & vbCrLf & _ "' 610-513-0066" & vbCrLf & _ " Dim myWS As Worksheet " & vbCrLf & _ " Dim myRange As Range " & vbCrLf & vbCrLf & _ " Dim i As Long " & vbCrLf & _ " Dim R As Long " & vbCrLf & _ " Dim mySortCol As Long " & vbCrLf & _ " Dim mySortOrder As Long " & vbCrLf & _ " Dim myWeight As Long " & vbCrLf & _ " Dim myLineStyle As Long " & vbCrLf & _ " Dim myCallerName As string " & vbCrLf & vbCrLf & _ " Static subTotals_Removed As Boolean " & vbCrLf & _ " Static rowNum_LastData_Revised As Long " & vbCrLf & vbCrLf & _ " Const rowNum_FirstData As Long = " ' concat theRowNum_DataFirst Const myMacroCode03 As String = " Const rowNum_LastData_Begin As Long = " Const myMacroCode04 As String = " Const colNum_FirstData As Long = " Const myMacroCode05 As String = " Const colNum_LastData As Long = " Const myMacroCode06 As String = " Const colNum_SubTotals As Long = " Const myMacroCode07 As String = " Const subTotals_Label As String = """ Const myMacroCode08 As String = _ " Set myWS = ActiveSheet " & vbCrLf & vbCrLf & _ "' ---------------------------------------------------------" & vbCrLf & _ "' If creating routine has specified subtotals exist," & vbCrLf & _ "' empty out entire line for each subtotal" & vbCrLf & _ "' " & vbCrLf & _ "' We *could* delete those lines, but that would introduce" & vbCrLf & _ "' the complexities of adjusting boxes around data and we" & vbCrLf & _ "' don't want to go there..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " If subTotals_Removed = False Then " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Begin " & vbCrLf & _ " If ((Len(subTotals_Label & """") 0) And (colNum_SubTotals 0)) Then " & vbCrLf & _ "' First take care of last row if it is a subtotal, preserving any bold bottom border" & vbCrLf & _ " R = rowNum_LastData_Revised " & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " myWeight = .Weight" & vbCrLf & _ " myLineStyle = .LineStyle" & vbCrLf & _ " End With" Const myMacroCode09 As String = _ " .Rows(rowNum_LastData_Revised).Delete" & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1" & vbCrLf & _ " R = rowNum_LastData_Revised" & vbCrLf & _ " Set myRange = .Range(.Cells(R, colNum_FirstData), .Cells(R, colNum_LastData))" & vbCrLf & _ " With myRange.Borders(xlEdgeBottom)" & vbCrLf & _ " .Weight = myWeight" & vbCrLf & _ " .LineStyle = myLineStyle" & vbCrLf & _ " End With" & vbCrLf & _ " End If" & vbCrLf & vbCrLf & _ "' Now deal with all the other subtotals " & vbCrLf & _ " For R = rowNum_FirstData To rowNum_LastData_Begin" & vbCrLf & _ " If .Cells(R, colNum_SubTotals).Value = subTotals_Label Then " & vbCrLf & _ " .Rows(R).Delete Shift:=xlDown " & vbCrLf & _ " rowNum_LastData_Revised = rowNum_LastData_Revised - 1 " & vbCrLf & _ " End If " & vbCrLf & _ " Next R " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " Else " & vbCrLf & _ " subTotals_Removed = True " & vbCrLf & _ " End If" & vbCrLf & _ " End If" & vbCrLf & _ " End With " & vbCrLf & vbCrLf Const myMacroCode10 As String = _ "' --------------------------------------------------------- " & vbCrLf & _ "' Do the sorting thing..." & vbCrLf & vbCrLf & _ " With myWS " & vbCrLf & _ " myCallerName = .Shapes(Application.Caller).Name" & vbCrLf & vbCrLf & _ " For i = colNum_FirstData To colNum_LastData" & vbCrLf & _ " On Error Resume Next 'User may have deleted 1 or more columns" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode11 As String = _ """ & Format$(i, ""000"") & ""Up"").Visible = False" & vbCrLf & _ " .Shapes(""" 'Concat mymacroname Const myMacroCode12 As String = _ """ & Format$(i, ""000"") & ""Dn"").Visible = False" & vbCrLf Const myMacroCode13 As String = _ " On Error GoTo 0 " & vbCrLf & _ " Next i" & vbCrLf & vbCrLf & _ " mySortCol = .Shapes(Application.Caller).TopLeftCell.Column " & vbCrLf & _ " Set myRange = .Range(.Cells(rowNum_FirstData, colNum_FirstData), .Cells(rowNum_LastData_Revised, colNum_LastData)) " & vbCrLf & vbCrLf Const myMacroCode14 As String = _ " If .Cells(rowNum_FirstData, mySortCol).Value < .Cells(rowNum_LastData_Revised, mySortCol).Value Then " & vbCrLf & _ " mySortOrder = xlDescending " & vbCrLf & _ " On Error Resume Next 'User may have deleted one or more arrows " & vbCrLf & _ " .Shapes(myCallerName & ""Dn"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " Else " & vbCrLf & _ " mySortOrder = xlAscending " & vbCrLf & _ " On Error Resume Next" & vbCrLf & _ " .Shapes(myCallerName & ""Up"").Visible = True" & vbCrLf & _ " On Error GoTo 0" & vbCrLf & _ " End If " & vbCrLf & vbCrLf & _ " myRange.Sort key1:=.Cells(rowNum_FirstData, mySortCol), order1:=mySortOrder " & vbCrLf & _ " End With " & vbCrLf & _ " End Sub " ' ------------------------------------------------------------------------ ' If there are not multiple data rows specified or if there are no data columns ' specified, call the whole thing off. 13010 If Abs((theRowNum_DataLast - theRowNum_DataFirst)) 0 Then 13011 If Abs((theColNum_ButtonLast - theColNum_ButtonFirst)) 0 Then 13012 okToProceed = True 13013 End If 13019 End If 13020 If okToProceed = True Then ' ------------------------------------------------------------------------ ' Check to see if we have a special macro name 13030 If Len(theMacroName & "") = 0 Then 13031 myMacroName = myDefaultMacroName 13032 Else 13033 myMacroName = theMacroName 13039 End If ' ------------------------------------------------------------------------ ' Create a code module in the target spreadsheet ' that will hold the code to handle our button click events 13040 Set myWB = theWS.Parent 13041 Set myParentModule = myWB.VBProject.VBComponents.Add(vbext_ct_StdModule ) 13049 Set myCodeModule = myParentModule.CodeModule 13050 myMacroCode = myMacroCode01 & myMacroName & _ myMacroCode02 & theRowNum_DataFirst & vbCrLf & _ myMacroCode03 & theRowNum_DataLast & vbCrLf & _ myMacroCode04 & theColNum_ButtonFirst & vbCrLf & _ myMacroCode05 & theColNum_DataLast & vbCrLf & _ myMacroCode06 & Val(theColNum_SubTotals & "") & vbCrLf & _ myMacroCode07 & theSubTotals_Label & Chr$(34) & vbCrLf & _ myMacroCode08 & vbCrLf & _ myMacroCode09 & _ myMacroCode10 & myMacroName & _ myMacroCode11 & myMacroName & _ myMacroCode12 & _ myMacroCode13 & _ myMacroCode14 13060 With myCodeModule 13061 .InsertLines .CountOfLines + 1, myMacroCode 13069 End With ' ------------------------------------------------------------------------ ' Now that we've got our macro code installed in the target Excel workbook, ' we loop through the worksheet's columns, creating a rectangle/ button ' and a couple of directional indicator arrows in each column header cell ' NB: If the text in a column header is right-justified, you'll need to ' have done a .IndentLevel=1 to slide it over far enough so the Up/Down ' arrows do not conflict with it 13070 With theWS 13071 Set myRange = .Range(.Cells(theRowNum_Buttons, theColNum_ButtonFirst), .Cells(theRowNum_Buttons, theColNum_ButtonLast)) 13079 For Each curCell In myRange.Cells 13080 With curCell 13081 curCellAddress = .Address(ReferenceStyle:=xlR1C1) 13084 Set curButton = .Parent.Shapes.AddShape(Type:=msoShapeRectangle, Top:=.Top, Height:=.Height, Width:=.Width, Left:=.Left) 13085 Set curUpArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13086 Set curDownArrow = .Parent.Shapes.AddShape(Type:=msoShapeIsoscelesTri angle, Top:=(.Top + .Height - myArrowHeight - 4), Height:=myArrowHeight, Width:=myArrowWidth, Left:=(.Left + .Width - myArrowWidth - 2)) 13089 End With 13090 curRI = RangeAddress_Parse(curCellAddress) 13099 curColNumString = Format$(curRI.ColLeft, "000") 13110 With curButton 13111 .Name = myMacroName & curColNumString 13112 .OnAction = myMacroName 13113 .Fill.Visible = msoFalse 13114 .Line.Visible = msoFalse 13115 .Placement = xlMoveAndSize 13119 End With 13120 With curUpArrow 13101 .Name = myMacroName & curColNumString & "Up" 13122 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13129 .Placement = xlMove 13130 With .Fill 13131 .Solid 13132 .ForeColor.SchemeColor = theArrowColor 13139 End With 13199 End With 13200 With curDownArrow 13201 .Name = myMacroName & curColNumString & "Dn" 13202 .Visible = msoFalse 'Arrows made visible/invisible by click event of the button. 13203 .Placement = xlMove 13209 .IncrementRotation 180 13211 With .Fill 13212 .Solid 13213 .ForeColor.SchemeColor = theArrowColor 13219 End With 13299 End With 13990 Next curCell 13991 End With 13999 End If SortButtons_Create_xit: DebugStackPop On Error Resume Next Set myRange = Nothing Set curCell = Nothing Set curButton = Nothing Set curDownArrow = Nothing Set curUpArrow = Nothing Set myParentModule = Nothing Set myCodeModule = Nothing Set myWB = Nothing Exit Sub SortButtons_Create_err: BugAlert True, "" Resume SortButtons_Create_xit End Sub -------------------------------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating functions | Excel Worksheet Functions | |||
Creating functions | Excel Programming | |||
Creating Functions | Excel Programming | |||
creating new functions | Excel Worksheet Functions | |||
Creating a functions | Excel Worksheet Functions |