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
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