converting excel 2003 to 2007 or recent version with VBA

5
votes

I have an xls excel file (excel 97-2003 workbook) in which there is vba code. I want to convert it to 2007 or newer version of excel with this vba code enabled. I have tried:

  1. save as macro-enabled.xlsm file
  2. changed the settings of macros from this blog. it changed the document in .xlsm but on values of cell (on which macro is running) it shows #NAME instead of values. orignal file screen shot: enter image description here

converted: enter image description here I am stuck here.

the code of macros:

1.moveAcross.bas

    Attribute VB_Name = "MoveAcross"
Sub GoToValues()
Attribute GoToValues.VB_Description = "Macro recorded 23-01-2001 by Murad Assaggaf"
Attribute GoToValues.VB_ProcData.VB_Invoke_Func = " \n14"
'
' GoToValues Macro
' Macro recorded 23-01-2001 by Murad Assaggaf
'

'
    ActiveWindow.SmallScroll ToRight:=13
    ActiveWindow.LargeScroll Down:=-2
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll ToRight:=2
    Range("X6").Select
End Sub
Sub ReturnToProfileArea()
Attribute ReturnToProfileArea.VB_Description = "Macro recorded 23-01-2001 by Murad Assaggaf"
Attribute ReturnToProfileArea.VB_ProcData.VB_Invoke_Func = " \n14"
'
' ReturnToProfileArea Macro
' Macro recorded 23-01-2001 by Murad Assaggaf
'

'
    Range("G5").Select
End Sub

2.Demand.bas

Attribute VB_Name = "Demand"
Function Demand(m0, m1, m2, m3, m4, m5, m6, EndInv, ST, Fraction)
    Static months(7) As Variant

    months(0) = m0
    months(1) = m1
    months(2) = m2
    months(3) = m3
    months(4) = m4
    months(5) = m5
    months(6) = m6

    Dim summy
    summy = 0

    If Fraction > 0 Then
        summy = months(ST + 1) * Fraction
    End If

    For n = 0 To ST
        summy = summy + months(n)
    Next n

    Demand = summy - EndInv

    If Demand < 0 Then
        Demand = 0
    End If
    End Function

3.Coverage.bas

Attribute VB_Name = "Coverage"
Function Coverage(m0, m1, m2, m3, m4, m5, m6, EndInv, ST, Fraction)

    Static months(7) As Variant

    months(0) = m0
    months(1) = m1
    months(2) = m2
    months(3) = m3
    months(4) = m4
    months(5) = m5
    months(6) = m6

    Dim summy
    summy = 0

    If Fraction > 0 Then
        summy = months(ST) * Fraction
    End If

    For n = 0 To (ST - 1)
        summy = summy + months(n)
    Next n

    Coverage = EndInv / (summy / (ST + Fraction))

End Function

Edited screenshot of VBA: enter image description here

2
vbaexcel
Help us to hep you, post the code that fails. – Gary's Student
@Gary'sStudent please check, i uploaded the code. – Noman Ahmad
Which one fails, where? Do they run properly but just return the #Name error? Are you putting one of your functions in that cell? Which one? – BruceWayne
@AxelRichter its =Demand.Demand and so on. but i tried while changing names but unfortunately not working in my case. – Noman Ahmad
Can you check in VB Editor --> Tools --> References if there are any libraries selected starting from [MISSING] or something similar? If yes, unselect it and then try. – Rufus

2 Answers

1
votes

Check in VB Editor --> Tools --> References if there are any libraries selected starting from MISSING: enter image description here

If yes, then unselect it and try again.

0
votes

You need to put the code in "ThisWorkbook" if you want to call it in a worksheet.