Excel 2000 macro to Excel 2016

**CAAE** · 06-04-2019, 02:50 PM

Hello,

I have just migrated to Win 10 and been told I can no longer keep Excel 2000 on my Win 10 machine.
I have some old macros that were written by someone else back in 2002 that I would like to have running but no longer work.
I believe it has to do with the ribbon interface.

Can anyone provide some help on where to get started and if this will be a simple insert or hours worth of development?

Sub StatFunctions()
'Creates toolbar for quick stat functions

Dim statBar As CommandBar
Dim aBtn As CommandBarControl

'Create toobar
Set statBar = CommandBars.Add(Name:="Stats", _
Position:=4, _
MenuBar:=False, _
temporary:=False)

'Populate the toolbar
With statBar.Controls
'Create standard deviation button
Set aBtn = .Add(Type:=msoControlButton, temporary:=False)
aBtn.FaceId = 98
aBtn.OnAction = "Stdev"
aBtn.Caption = "Standard Deviation"
aBtn.Tag = True

'Create variance button
Set aBtn = .Add(Type:=msoControlButton, temporary:=False)
aBtn.FaceId = 101
aBtn.OnAction = "Var"
aBtn.Caption = "Variance"
aBtn.Tag = True

'Create Median Button
Set aBtn = .Add(Type:=msoControlButton, temporary:=False)
aBtn.FaceId = 92
aBtn.OnAction = "Median"
aBtn.Caption = "Median"
aBtn.Tag = True

'Create Standard Error Button
Set aBtn = .Add(Type:=msoControlButton, temporary:=False)
aBtn.FaceId = 84
aBtn.OnAction = "Std_Error"
aBtn.Caption = "Standard Error"
aBtn.Tag = True
End With

End Sub
Function Stdev()

Dim answer As Double
Dim myRange As Range

Set myRange = Application.Selection

On Error GoTo BadValue

answer = Application.WorksheetFunction.Stdev(myRange)

MsgBox Prompt:=answer, Title:="Standard Deviation"

Exit Function

BadValue:
MsgBox Prompt:="No range selected", Buttons:=vbCritical, Title:="ERROR"
Exit Function

End Function
Function Var()
Dim answer As Double
Dim myRange As Range

Set myRange = Application.Selection

On Error GoTo BadValue

answer = Application.WorksheetFunction.Var(myRange)

MsgBox Prompt:=answer, Title:="Variance"

Exit Function

BadValue:
MsgBox Prompt:="No range selected", Buttons:=vbCritical, Title:="ERROR"
Exit Function

End Function
Function Median()
Dim answer As Double
Dim myRange As Range

On Error GoTo BadValue

Set myRange = Application.Selection

answer = Application.WorksheetFunction.Median(myRange)

MsgBox Prompt:=answer, Title:="Median"

Exit Function

BadValue:
MsgBox Prompt:="No range selected", Buttons:=vbCritical, Title:="ERROR"
Exit Function

End Function
Function Std_Error()

Dim answer As Double
Dim myRange As Range

Set myRange = Application.Selection

On Error GoTo BadValue

answer = ((Application.WorksheetFunction.Stdev(myRange)) / (Sqr(Application.WorksheetFunction.CountA(myRange))))

MsgBox Prompt:=answer, Title:="Standard Error"

Exit Function

BadValue:
MsgBox Prompt:="No range selected", Buttons:=vbCritical, Title:="ERROR"
Exit Function

End Function

Thanks so much for any advice,
Carol

**JLGWhiz** · 06-04-2019, 07:06 PM

Some old macros will run in newer versions, but others which use old Excel objects that do not exist in the current version will need to be updated to use curren object names and attributes. One way to do the updates is to try and run the macro and as the code errors, fix the object or property that the new version does not like. In some cases, it might be easier to write new code altogether, but I would take it on a case by case basis. You can always post back to the forum for help on specific problems, but I don't believe anyone wants to take on the task of re-writing your macros for you.

**mjr veverka** · 06-04-2019, 10:37 PM

Originally Posted by CAAE

... but no longer work ...

This way of calling a new/user menu should work in the old and new excel (although it does not have to), try it:

1. ThisWorkbook module:

Please Login or Register  to view this content.

2. Standard module, e.g. Module1:

Please Login or Register  to view this content.

3. You can also simplify functions calls a little and shorten them to one function, e.g.:
(in standard module Module1 also)

Please Login or Register  to view this content.

But do not treat it as a solution to your problem, but an attempt to solve it, an example.

After starting the workbook, a new icon should appear in the main menu named "Extras" or "Add-Ins", don't know which one.
Your new menu for statistics will be located there ... maybe ...

Ps.:
Do not use the names of the excel functions to name the udf functions, because in some cases will be one big ... "big ben", i.e. boom boom ...

... with calculations.