Results 1 to 2 of 2
  1. #1


    Member Since
    May 28, 2012
    Posts
    1
    I have a VBA issue
    I am trying to run a VBA in excel in Lion/excel 2011 that has been written in Windows 7/Excel 2010.
    It is giving me a Run-time error "448"
    Named argument not found

    Module 1:

    Option Explicit
    'Show UserForm
    Sub ShwFrm()
    UserForm1.Show
    End Sub

    'Calculate and populate new properties
    Sub PopProperties()
    Application.ScreenUpdating = False
    Dim PctDone As Single
    'Declarations
    Dim SheetName As String
    Dim StrM As Variant, EndM As Variant, StrCol As Long, MthRw As Long, i As Long, EndCol As Long
    Dim AvailBalRw As Long, NewPropRw As Long
    'Initializations
    SheetName = "Model_Summary_Fund(I)"
    MthRw = 15
    AvailBalRw = 19
    NewPropRw = 20
    StrM = Sheets(SheetName).Range("E12").Value
    EndM = Sheets(SheetName).Range("J12").Value
    'Mandatory field Validations
    If StrM = "" Then
    Unload UserForm1
    MsgBox "Please enter ""Start Max Purchase Month"" and continue.", vbCritical, "Start Month Required"
    Exit Sub
    ElseIf EndM = "" Then
    Unload UserForm1
    MsgBox "Please enter ""Finish Max Purchase Month"" and continue.", vbCritical, "Finish Month Required"
    Exit Sub
    End If
    'Numeric Validations
    If IsNumeric(StrM) = False Then
    Unload UserForm1
    MsgBox """Start Max Purchase Month"" should be a Number.", vbCritical, "Invalid Start Month"
    Exit Sub
    ElseIf IsNumeric(EndM) = False Then
    Unload UserForm1
    MsgBox """Finish Max Purchase Month"" should be a Number.", vbCritical, "Invalid Finish Month"
    Exit Sub
    End If
    'Find Column Number of Start Month and End Month in Row 15
    StrCol = FindCol(SheetName, MthRw, StrM)
    EndCol = FindCol(SheetName, MthRw, EndM)
    'Check if Start Month and End Month Column exist in the WorkSheet
    If StrCol = 0 Then
    Unload UserForm1
    MsgBox "The entered ""Start Max Purchase Month"" cannot be found.", vbCritical, "Start Month does not exist"
    Exit Sub
    ElseIf EndCol = 0 Then
    Unload UserForm1
    MsgBox "The entered ""Finish Max Purchase Month"" cannot be found.", vbCritical, "Finish Month does not exist"
    Exit Sub
    End If
    'If all good, loop through the Columns, calculate and populate the number of new properties
    For i = StrCol To EndCol
    Call CalNewProp(SheetName, AvailBalRw, NewPropRw, i)
    'For Progress bar
    PctDone = i / EndCol
    UpdateProgressBar PctDone
    Next
    Unload UserForm1
    MsgBox "New Properties populated successfully. Happy days!", vbInformation, "Success"
    Application.ScreenUpdating = True
    End Sub
    'Calculate number of new properties
    Sub CalNewProp(SheetName, AvailBalRw, NewPropRw, MthCol)
    Dim lCount As Long, AvailBal As Double
    lCount = 0
    Do
    lCount = lCount + 1
    Sheets(SheetName).Cells(NewPropRw, MthCol).Value = lCount
    AvailBal = Sheets(SheetName).Cells(AvailBalRw, MthCol).Value
    Loop While AvailBal >= 0
    Sheets(SheetName).Cells(NewPropRw, MthCol).Value = lCount - 1
    End Sub

    'Find the Column Numbers of Start and Finish Month
    Function FindCol(SheetName, FRw, FindVal)
    Dim rFound As Range
    'On Error Resume Next
    Set rFound = Sheets(SheetName).Rows(FRw).Find(What:=FindVal, After:=Sheets(SheetName).Cells(FRw, 1), LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
    'On Error GoTo 0
    If Not rFound Is Nothing Then
    FindCol = rFound.Column
    Exit Function
    Else
    FindCol = 0
    End If
    End Function
    'Update the progress bar
    Sub UpdateProgressBar(PctDone As Single)
    With UserForm1
    ' Update the Caption property of the Frame control.
    .FrameProgress.Caption = Format(PctDone, "0%")
    ' Widen the Label control.
    .LabelProgress.Width = PctDone * _
    (.FrameProgress.Width - 10)
    End With
    ' The DoEvents allows the UserForm to update.
    DoEvents
    End Sub


    User Form:

    'Call Populate function
    Private Sub UserForm_Activate()
    ' Set the width of the progress bar to 0.
    UserForm1.LabelProgress.Width = 0
    ' Call the main subroutine.
    If ThisWorkbook.ActiveSheet.Name = "Model_Summary_Fund(I)" Then
    Call Module1.PopProperties
    ElseIf ThisWorkbook.ActiveSheet.Name = "Model_Summary_Fund(2)" Then
    Call Module2.PopProperties
    ElseIf ThisWorkbook.ActiveSheet.Name = "Model_Summary_SS" Then
    Call Module3.PopProperties
    End If
    End Sub

  2. #2

    chscag's Avatar
    Member Since
    Jan 23, 2008
    Location
    Keller, Texas
    Posts
    49,395
    Specs:
    Late 2013 27" iMac, iPad 3, iPhone 6s+, iPhone 6+, 3 iPods, Sierra
    I don't know if this will help or not, but most macros ported from Windows Office 2010 to Mac Office 2011 have to be recompiled. I don't use Excel so I can't help you with your code, however, check out this web site where some of the differences in code are shown.

    LINK

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vba newbie HELP!!!!!
    By kevinless in forum Switcher Hangout
    Replies: 8
    Last Post: 10-31-2012, 10:56 AM
  2. VBA ExportAsFixedFormat Error
    By YortChris in forum OS X - Operating System
    Replies: 0
    Last Post: 11-21-2011, 05:32 PM
  3. Excel VBA support on macbook Pro
    By pipeweed in forum Apple Notebooks
    Replies: 0
    Last Post: 04-27-2010, 06:31 AM
  4. excel 2008 = NO VBA :-(
    By j4ymf in forum Switcher Hangout
    Replies: 7
    Last Post: 07-25-2009, 12:40 AM
  5. Replies: 2
    Last Post: 01-05-2007, 06:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •