Adding a variable returns error

M

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,644
Office Version
  1. 365
Platform
  1. Windows
I am trying to add a variable to a routine but VB won't let me add it and returns an 'Out of Memory' error and removes the variable.

Not entirely sure why it's doing this as I don't have a massive number of public or local variables declared and the module isn't especially large.

I am setting a lot of range variables to manipulate data and move around various sheets without having to activate the relevant sheets and I'm wondering of this is causing a problem. I have, as far as I can see, set all range variables to 'Nothing' when finished with, but when set to 'Nothing' some will be re-used again as part of a loop routine.


Thanks
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
mikeymay said:
I am trying to add a variable to a routine but VB won't let me add it and returns an 'Out of Memory' error and removes the variable.
Click to expand...
Can you post the routine's code and also show us how you were trying to add the variable to it? That way we can try it out for ourselves while trying to debug the problem.
 
Upvote 0
Code: 
Option Explicit

Public boolCancel As Boolean
Public strYear As String
Public strMonth As String
Public dtMonthEnd As Date
Public dtPriorMonthEnd As Date
Public strPriorYear As String
Public intMonth As Integer
Public intMonthOffset As Integer
Public intPriorMonth As Integer

Sub LBoSPAReturn()

Dim wbHRISDataFile As Workbook
Dim wbPAReturnFile As Workbook
Dim rngHRISDataStart As Range
Dim rngHRISDataAll As Range
Dim rngHRISDataReturnStart As Range
Dim rngHRISDataReturnAll As Range
Dim rngHRISDataReturn As Range
Dim rngHRISDataErrorCount As Range
Dim rngHRISDataAreaErrorCount As Range
Dim rngAreaListSendArea As Range
Dim rngBudgetsData As Range
Dim strBrand As String
Dim strChannel As String
Dim strNetwork As String
Dim strRegion As String
Dim strLDG As String
Dim currBudgetMonth1 As Currency
Dim currBudgetMonth2 As Currency
Dim currBudgetMonth3 As Currency
Dim currBudgetMonth4 As Currency
Dim currBudgetMonth5 As Currency
Dim currBudgetMonth6 As Currency
Dim currBudgetMonth7 As Currency
Dim currBudgetMonth8 As Currency
Dim currBudgetMonth9 As Currency
Dim currBudgetMonth10 As Currency
Dim currBudgetMonth11 As Currency
Dim currBudgetMonth12 As Currency
Dim rngHRISDataEmpRef As Range
Dim rngHRISDataBrand As Range
Dim rngHRISDataChannel As Range
Dim rngHRISDataNetwork As Range
Dim rngHRISDataRegion As Range
Dim rngHRISDataLDG As Range
Dim strTest As String
Dim rngDataBudget As Range
Dim rngPAHRISDataReturnStart As Range
Dim intCount As Integer
Dim rngAreaMoveDataStart As Range
Dim rngAreaMoveSummaryDataStart As Range
Dim rngStaffRefEmpRef As Range
Dim rngStaffRefBrand As Range
Dim rngStaffRefChannel As Range
Dim rngStaffRefNetwork As Range
Dim rngStaffRefRegion As Range
Dim rngStaffRefLDG As Range
Dim strPriorFile As String
Dim objPriorPeriodFile As Object
Dim boolPriorPeriodFile As Boolean
Dim wbPriorFile As Workbook
Dim intRowCount As Integer
Dim objFolder As Object
Dim strFolder As String
Dim boolPriorPeriodInflow As Boolean
Dim boolPriorPeriodAreaMove As Boolean
'trying to add the variable here

frmSelectDetails.Show

If boolCancel = True Then
    MsgBox ("The process has been cancelled."), vbInformation
    Exit Sub
    Else
End If

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Selection = "hello"

Sheets("HRIS Data for Return").Select

Set rngHRISDataErrorCount = Range("HRISData_ErrorCount") 'cleared

If WorksheetFunction.Sum(rngHRISDataErrorCount) > 0 Then
    MsgBox ("There are 'Cost Codes' on the HRIS download that are not on the 'Structure' sheet." & vbCr & vbCr & _
        "Check column AG on the 'HRIS Data' sheet and ensure the data on the 'Structure' sheet is up to date." & vbCr & vbCr & _
        "No data has been sent to the Area PA's and this process will now end."), vbCritical
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Set rngHRISDataErrorCount = Nothing
    Set rngHRISDataReturnStart = Nothing
    Exit Sub
    Else
End If

Set rngHRISDataErrorCount = Nothing

Set rngHRISDataAreaErrorCount = Range("HRISData_AreaErrorCount") 'cleared

If rngHRISDataAreaErrorCount > 0 Then
    MsgBox ("There are areas on the HRIS data with no relevant PA.." & vbCr & vbCr & _
        "Check column AL on the 'HRIS Data' sheet and ensure the 'PA List' sheet is up to date." & vbCr & vbCr & _
        "No data has been sent to the Area PA's and this process will now end."), vbCritical
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Set rngHRISDataAreaErrorCount = Nothing
    Set rngHRISDataReturnStart = Nothing
    Exit Sub
    Else
End If

Set rngHRISDataAreaErrorCount = Nothing

Set wbHRISDataFile = ActiveWorkbook 'cleared

Set rngHRISDataStart = Range("HRISData_DataStart") 'cleared
Set rngAreaListSendArea = Range("AreaList_SendStart") 'cleared
Set rngHRISDataEmpRef = Range(rngHRISDataStart, rngHRISDataStart.Offset(0, -8).End(xlDown).Offset(0, 8))
Set rngHRISDataBrand = rngHRISDataEmpRef.Offset(0, 3) 'cleared
Set rngHRISDataChannel = rngHRISDataEmpRef.Offset(0, 4) 'cleared
Set rngHRISDataNetwork = rngHRISDataEmpRef.Offset(0, 5) 'cleared
Set rngHRISDataRegion = rngHRISDataEmpRef.Offset(0, 6) 'cleared
Set rngHRISDataLDG = rngHRISDataEmpRef.Offset(0, 7) 'cleared
Set rngHRISDataAll = Range(rngHRISDataEmpRef, rngHRISDataLDG.Offset(0, 2)) 'cleared

Do Until rngAreaListSendArea = ""
    If rngAreaListSendArea = "No" Then
        Else
        Set rngHRISDataReturnStart = Range("HRISDataReturn_DataStart").Offset(1, 0) 'cleared
        Range(rngHRISDataReturnStart, rngHRISDataReturnStart.End(xlDown).End(xlToRight)).Select
        Selection.ClearContents
        strBrand = rngAreaListSendArea.Offset(0, -6)
        strChannel = rngAreaListSendArea.Offset(0, -5)
        strNetwork = rngAreaListSendArea.Offset(0, -4)
        strRegion = rngAreaListSendArea.Offset(0, -3)
        strLDG = rngAreaListSendArea.Offset(0, -2)
        
        strFolder = "C:\Documents and Settings\423457\" & "Desktop\Work in Progress - Tansfer to Network\LBoS PA Return\Current Month Returns\" & strBrand
        
        Set objFolder = CreateObject("Scripting.FileSystemObject") 'cleared
        If objFolder.FolderExists(strFolder) Then
            strFolder = strFolder & "\" & strChannel
            If objFolder.FolderExists(strFolder) Then
                strFolder = strFolder & "\" & strNetwork
                If objFolder.FolderExists(strFolder) Then
                    strFolder = strFolder & "\" & strRegion
                    If objFolder.FolderExists(strFolder) Then
                        Else
                        objFolder.createfolder (strFolder)
                    End If
                    Else
                    objFolder.createfolder (strFolder)
                    strFolder = strFolder & "\" & strRegion
                    objFolder.createfolder (strFolder)
                End If
                Else
                objFolder.createfolder (strFolder)
                strFolder = strFolder & "\" & strNetwork
                objFolder.createfolder (strFolder)
                strFolder = strFolder & "\" & strRegion
                objFolder.createfolder (strFolder)
            End If
            Else
            objFolder.createfolder (strFolder)
            strFolder = strFolder & "\" & strChannel
            objFolder.createfolder (strFolder)
            strFolder = strFolder & "\" & strNetwork
            objFolder.createfolder (strFolder)
            strFolder = strFolder & "\" & strRegion
            objFolder.createfolder (strFolder)
        End If
        
        Set objFolder = Nothing
        
        Set rngBudgetsData = Range("Budgets_DataStart") 'cleared
        
        Do Until rngBudgetsData = strBrand And rngBudgetsData.Offset(0, 1) = strChannel And rngBudgetsData.Offset(0, 3) = strRegion And rngBudgetsData.Offset(0, 3) = strRegion And rngBudgetsData.Offset(0, 4) = strLDG
            Set rngBudgetsData = rngBudgetsData.Offset(1, 0)
        Loop
        
        currBudgetMonth1 = rngBudgetsData.Offset(0, 6 + intMonthOffset)
        currBudgetMonth2 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 1)
        currBudgetMonth3 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 2)
        currBudgetMonth4 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 3)
        currBudgetMonth5 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 4)
        currBudgetMonth6 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 5)
        currBudgetMonth7 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 6)
        currBudgetMonth8 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 7)
        currBudgetMonth9 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 8)
        currBudgetMonth10 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 9)
        currBudgetMonth11 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 10)
        currBudgetMonth12 = rngBudgetsData.Offset(0, 6 + intMonthOffset + 11)
        
        Set rngBudgetsData = Nothing
        
        rngHRISDataAll.Copy
        rngHRISDataReturnStart.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        Set rngHRISDataReturnAll = Range(rngHRISDataReturnStart, rngHRISDataReturnStart.End(xlDown).Offset(0, 9)) 'cleared
        
        rngHRISDataReturnAll.Sort Key1:=Range("D7"), Order1:=xlAscending, Key2:=Range("E7") _
            , Order2:=xlAscending, Key3:=Range("G7"), Order3:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
            
        Set rngHRISDataReturnAll = Nothing
        
        Set rngHRISDataReturn = rngHRISDataReturnStart 'cleared
        
        Do Until rngHRISDataReturn = ""
            If rngHRISDataReturn.Offset(0, 3) = strBrand And rngHRISDataReturn.Offset(0, 4) = strChannel And rngHRISDataReturn.Offset(0, 6) = strRegion Then
                Do Until rngHRISDataReturn.Offset(0, 3) <> strBrand Or rngHRISDataReturn.Offset(0, 4) <> strChannel Or rngHRISDataReturn.Offset(0, 6) <> strRegion
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
                Else
                Do Until rngHRISDataReturn.Offset(0, 3) = strBrand And rngHRISDataReturn.Offset(0, 4) = strChannel And rngHRISDataReturn.Offset(0, 6) = strRegion
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
                Set rngHRISDataReturnStart = rngHRISDataReturn
                Range(rngHRISDataReturn.Offset(-1, 0), rngHRISDataReturn.End(xlUp).Offset(1, 0).EntireRow).Select
                Selection.Delete
                Do Until rngHRISDataReturn.Offset(0, 3) <> strBrand Or rngHRISDataReturn.Offset(0, 4) <> strChannel Or rngHRISDataReturn.Offset(0, 6) <> strRegion
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
            End If
            If rngHRISDataReturn = "" Then
                Else
                Range(rngHRISDataReturn, rngHRISDataReturn.End(xlDown).EntireRow).Select
                Selection.Delete
                Set rngHRISDataReturn = ActiveCell
            End If
        Loop
        Set rngHRISDataReturnAll = Range(rngHRISDataReturnStart, rngHRISDataReturnStart.End(xlDown).Offset(0, 9))
        rngHRISDataReturnAll.Sort Key1:=Range("H7"), Order1:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
        Set rngHRISDataReturn = rngHRISDataReturnStart
        Do Until rngHRISDataReturn = ""
            If rngHRISDataReturn.Offset(0, 7) = strLDG Then
                Do Until rngHRISDataReturn.Offset(0, 7) <> strLDG
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
                Else
                Do Until rngHRISDataReturn.Offset(0, 7) = strLDG
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
                Set rngHRISDataReturnStart = rngHRISDataReturn
                Range(rngHRISDataReturn.Offset(-1, 0), rngHRISDataReturn.End(xlUp).Offset(1, 0).EntireRow).Select
                Selection.Delete
                Do Until rngHRISDataReturn.Offset(0, 7) <> strLDG
                    Set rngHRISDataReturn = rngHRISDataReturn.Offset(1, 0)
                Loop
                If rngHRISDataReturn = "" Then
                    Else
                    Range(rngHRISDataReturn, rngHRISDataReturn.End(xlDown).EntireRow).Select
                    Selection.Delete
                    Set rngHRISDataReturn = ActiveCell
                End If
            End If
        Loop
        
        Set wbPAReturnFile = Workbooks.Open("C:\Documents and Settings\423457\Desktop\Work in Progress - Tansfer to Network\LBoS PA Return\LBoS PA Update File.xls")
        
        Range("Today") = dtMonthEnd
        Range("Brand") = strBrand
        Range("Channel") = strChannel
        Range("Network") = strNetwork
        Range("Region") = strRegion
        Range("LDG") = strLDG
        
        Set rngDataBudget = Range("BudgetJanCurrYear") 'cleared
        rngDataBudget.Offset(intMonth, 0) = currBudgetMonth1
        rngDataBudget.Offset(intMonth + 1, 0) = currBudgetMonth2
        rngDataBudget.Offset(intMonth + 2, 0) = currBudgetMonth3
        rngDataBudget.Offset(intMonth + 3, 0) = currBudgetMonth4
        rngDataBudget.Offset(intMonth + 4, 0) = currBudgetMonth5
        rngDataBudget.Offset(intMonth + 5, 0) = currBudgetMonth6
        rngDataBudget.Offset(intMonth + 6, 0) = currBudgetMonth7
        rngDataBudget.Offset(intMonth + 7, 0) = currBudgetMonth8
        rngDataBudget.Offset(intMonth + 8, 0) = currBudgetMonth9
        rngDataBudget.Offset(intMonth + 9, 0) = currBudgetMonth10
        rngDataBudget.Offset(intMonth + 10, 0) = currBudgetMonth11
        rngDataBudget.Offset(intMonth + 11, 0) = currBudgetMonth12
        
        Set rngDataBudget = Nothing
        
        Set rngStaffRefEmpRef = Range("StaffRef_EmpRefStart") 'cleared
        Set rngStaffRefBrand = Range("StaffRef_BrandStart") 'cleared
        Set rngStaffRefChannel = Range("StaffRef_ChannelStart") 'cleared
        Set rngStaffRefNetwork = Range("StaffRef_NetworkStart") 'cleared
        Set rngStaffRefRegion = Range("StaffRef_RegionStart") 'cleared
        Set rngStaffRefLDG = Range("StaffRef_LDGStart") 'cleared
        
        rngHRISDataEmpRef.Copy
        rngStaffRefEmpRef.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        rngHRISDataBrand.Copy
        rngStaffRefBrand.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        rngHRISDataChannel.Copy
        rngStaffRefChannel.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        rngHRISDataNetwork.Copy
        rngStaffRefNetwork.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        rngHRISDataRegion.Copy
        rngStaffRefRegion.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        rngHRISDataLDG.Copy
        rngStaffRefLDG.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        Set rngPAHRISDataReturnStart = Range("HRISData_DataStart") 'cleared
        
        rngHRISDataReturnAll.Copy
        rngPAHRISDataReturnStart.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        Set rngHRISDataReturnAll = Nothing
        
        Set rngPAHRISDataReturnStart = Nothing
                
        Set rngStaffRefEmpRef = Nothing
        Set rngStaffRefBrand = Nothing
        Set rngStaffRefChannel = Nothing
        Set rngStaffRefNetwork = Nothing
        Set rngStaffRefRegion = Nothing
        Set rngStaffRefLDG = Nothing
        
        intCount = Range("HRISData_Count")
        
        Sheets("Area Movement").Select
        
        Set rngAreaMoveDataStart = Range("AreaMovement_DataStart")
        
        Range(rngAreaMoveDataStart.Offset(intCount, 0), rngAreaMoveDataStart.End(xlDown).EntireRow).Delete
        
        Set rngAreaMoveDataStart = Nothing
        
        Sheets("Area Movement Summary").Select
        Set rngAreaMoveSummaryDataStart = Range("AreaMovementSummary_DataStart")
        Range(rngAreaMoveSummaryDataStart.Offset(intCount, 0), rngAreaMoveSummaryDataStart.End(xlDown).EntireRow).Delete
        
        Set rngAreaMoveSummaryDataStart = Nothing
        
        strPriorFile = "C:\Documents and Settings\423457\" & "Desktop\Work in Progress - Tansfer to Network\LBoS PA Return\Prior Month Returns\" & strBrand & "\" & strChannel & "\" & _
            strNetwork & "\" & strRegion & "\" & Replace(strLDG, " & ", "  ") & " " & Format(dtPriorMonthEnd, "mmm yy") & ".xls"
           
        Set objPriorPeriodFile = CreateObject("Scripting.FileSystemObject") 'cleared
        
        If Not objPriorPeriodFile.FileExists(strPriorFile) Then
            boolPriorPeriodFile = False
            Else
            boolPriorPeriodFile = True
        End If

        Set objPriorPeriodFile = Nothing
        
        If boolPriorPeriodFile = True Then
            Set wbPriorFile = Workbooks.Open(strPriorFile, UpdateLinks:=0) 'cleared
    
            wbPriorFile.Sheets("Inflow").Activate
            
            Range("Inflow_DataStart").Select
               
            intRowCount = 0
            
            If Selection.Offset(0, 1) = "" Then
                boolPriorPeriodInflow = False
                Else
                boolPriorPeriodInflow = True
                Do Until Selection.Offset(intRowCount, 1) = ""
                    intRowCount = intRowCount + 1
                Loop
                Range(Selection, Selection.Offset(intRowCount - 1, 7)).Copy
                wbPAReturnFile.Sheets("Previous Inflow").Activate
                Range("A11").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
                
            wbPriorFile.Sheets("Area Movement Summary").Activate
            
            Range("A10").Select
                      
            intRowCount = 0
            
            If Selection = "" Then
                boolPriorPeriodAreaMove = False
                Else
                boolPriorPeriodAreaMove = True
                Do Until Selection.Offset(intRowCount, 0) = ""
                    intRowCount = intRowCount + 1
                Loop
                Range(Selection, Selection.Offset(intRowCount - 1, 10)).Copy
                wbPAReturnFile.Sheets("Previous Area Movements").Activate
                Range("A11").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
            Application.CutCopyMode = False
            wbPriorFile.Close savechanges:=False
            Set wbPriorFile = Nothing
            Else
        End If
        
        Sheets("HRIS Data").Visible = xlVeryHidden
        Sheets("Previous Area Movements").Visible = xlVeryHidden
        Sheets("Previous Inflow").Visible = xlVeryHidden
        Sheets("Lookups").Visible = xlVeryHidden
        Sheets("Staff Ref").Visible = xlVeryHidden
        Sheets("Data").Visible = xlVeryHidden
        Sheets("Summary").Activate
        wbPAReturnFile.SaveAs "C:\Documents and Settings\423457\" & "Desktop\Work in Progress - Tansfer to Network\LBoS PA Return\Current Month Returns\" & strBrand & "\" & strChannel & "\" & strNetwork & "\" & strRegion & "\" & Replace(strLDG, " & ", "  ") & " " & Format(dtMonthEnd, "mmm yy") & ".xls"
        wbPAReturnFile.Close
        Set wbPAReturnFile = Nothing
    End If
    Set rngAreaListSendArea = rngAreaListSendArea.Offset(1, 0)
    Set rngHRISDataReturn = Nothing
    Set rngHRISDataReturnStart = Nothing
Loop

Set rngHRISDataEmpRef = Nothing
Set rngHRISDataBrand = Nothing
Set rngHRISDataChannel = Nothing
Set rngHRISDataNetwork = Nothing
Set rngHRISDataRegion = Nothing
Set rngHRISDataLDG = Nothing
Set rngHRISDataAll = Nothing

Set rngHRISDataStart = Nothing

Set rngAreaListSendArea = Nothing

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

Set wbHRISDataFile = Nothing

End Sub
 
Upvote 0
I am also getting the same error when i try to add certain code in the module....

adding the following
Code: 
strTest = Application.MemoryUsed
does exactly the same thing and returns the error message box and removes the code typed in.
 
Upvote 0
I did find that page myself but it hasn't helped really. Tried closing and re opening Excel and that didn't work either.

As IT would say 'Have you re-booted?'.........so time to finish for the day and check tomorrow.

Thanks
 
Upvote 0
mikeymay said:
I did find that page myself but it hasn't helped really. Tried closing and re opening Excel and that didn't work either.

As IT would say 'Have you re-booted?'.........so time to finish for the day and check tomorrow.

Thanks
Click to expand...
The re-boot may be a good idea. If you have anything "leaking" memory (where in Excel or not)... that may be affecting Excel's ability to manage its own memory. Let us know how it works out for you. Perhaps knowing whether it works or not will give one of the volunteers here familiar with memory issues something to go on in giving you a direction to follow.
 
Upvote 0
mikeymay

Is that all the code?

Or all the code in a module?

Or an example of one of the subs?

There are some things you could do in general to shorten it.

For example variables like this:
Code: 
Dim currBudgetMonth1 As Currency
Dim currBudgetMonth2 As Currency
Dim currBudgetMonth3 As Currency
Dim currBudgetMonth4 As Currency
Dim currBudgetMonth5 As Currency
Dim currBudgetMonth6 As Currency
Dim currBudgetMonth7 As Currency
Dim currBudgetMonth8 As Currency
Dim currBudgetMonth9 As Currency
Dim currBudgetMonth10 As Currency
Dim currBudgetMonth11 As Currency
Dim currBudgetMonth12 As Currency
Could be replace with an array:
Code: 
Dim currBudgetMonthArr(1 To 12) As Currency

When it comes to working with the array just add a little looping.
Code: 
For I = 1 To 12
   currBudgetMonthArr(I) = rngBudgetsData.Offset(0,6 +intMonthOffset+(I-1))
Next I
There's a few other things that might be worth a look:

A userform is opened right at the start of the code but nothing appears to happen with it.

You can use the lowly Dir to check for the existence of a file, no real need to create an instance of the FSO.

In the (last?) section of the code where you open a workbook there's a lot of Selection/Activating going on.


The above might have nothing to do with the problem but it might be worth checking them out.:)
 
Upvote 0
Thanks all for the advice.

Just logged on so will keep testing the process during the devlopment and let you know.

I am hoping it is down to some memory leakage but I will have to address this for when the final process goes live.


Thanks
 
Upvote 0
Bug*er....

Re boot hasn't worked and I still can't add any new variables.

Will try implementing your suggestion Norie and I'll let you know how it works.

With regards to any further code
Code: 
Private Sub btnCancel_Click()

boolCancel = True

frmSelectDetails.Hide

End Sub

Private Sub btnOK_Click()

frmSelectDetails.Hide

strYear = cbYear
strMonth = cbMonth

dtMonthEnd = "1 " & strMonth & " " & strYear
intMonthOffset = Month(dtMonthEnd)
dtPriorMonthEnd = dtMonthEnd - 1
strPriorYear = Year(dtPriorMonthEnd)

intMonth = Month(dtMonthEnd)

If intMonth = 12 Then
    dtMonthEnd = "31/12/" & strYear
    strPriorYear = Year(dtMonthEnd)
    Else
    dtMonthEnd = "1 " & intMonth + 1 & " " & strYear
    dtMonthEnd = dtMonthEnd - 1
End If

intPriorMonth = Month(dtPriorMonthEnd)

cbYear = ""
cbMonth = ""

End Sub
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Test if WB is <No Variables>
Replies
6
Views
249
MARK858
MARK858
K
  • Question Question
Run-time error '-2147217900 (80040e14)': Syntax error or access violation
Replies
0
Views
120
kristianb63
K
E
  • Solved
Is there a preference to call/address variables vs cells?
Replies
6
Views
279
EssKayKay
E
J
  • Question Question
Reorganize VB Code into modules and create public variables
Replies
1
Views
473
Artik
Artik
KlausW
  • Solved
import a sheet from a closed workbook from location in cell
Replies
3
Views
367
bobsan42
bobsan42

Forum statistics

Threads
1,224,531
Messages
6,179,380
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top