SamT
05-28-2016, 04:06 PM
So, I was looking at some code for an unfamiliar Application and started daydreaming. . .:bug:
What would it be like coding an Excel Project if in the Workbook_Open sub we set the following Public Object variables if they would be used in the VBA Project?
Set xlApp = Me.Parent
Set WkBk = Me
Set WkShFn = xlApp.WorksheetFunction
Set vbFilemanager = FileSystemObject
Set OrderEntry = Sheets("New Order")
Set Products = Sheets("Product List")
Set Prices = Sheets("Price List")
With ImportantSheets 'Collection
.Add OrderEntry
.Add Products
.Add Prices
.Add Sheets("Sheet4")
'.Add Etc
End With
My thoughts are: Memory is cheap. There is even talk of phasing out the Virtual Memory Paging system, except that too many extant programs depend on it.
It should improve the efficiency of the Project. How many times in a large VBA Project do we create and kill one of those variables? The cost is that memory block being permanently assigned. Another payoff is fewer CPU cycles used.
It should improve the efficiency of the coder because those short, meaningful variable names always refer to the same object everywhere in code and he/she never again has to write specific references to the Objects.
What would it be like coding an Excel Project if in the Workbook_Open sub we set the following Public Object variables if they would be used in the VBA Project?
Set xlApp = Me.Parent
Set WkBk = Me
Set WkShFn = xlApp.WorksheetFunction
Set vbFilemanager = FileSystemObject
Set OrderEntry = Sheets("New Order")
Set Products = Sheets("Product List")
Set Prices = Sheets("Price List")
With ImportantSheets 'Collection
.Add OrderEntry
.Add Products
.Add Prices
.Add Sheets("Sheet4")
'.Add Etc
End With
My thoughts are: Memory is cheap. There is even talk of phasing out the Virtual Memory Paging system, except that too many extant programs depend on it.
It should improve the efficiency of the Project. How many times in a large VBA Project do we create and kill one of those variables? The cost is that memory block being permanently assigned. Another payoff is fewer CPU cycles used.
It should improve the efficiency of the coder because those short, meaningful variable names always refer to the same object everywhere in code and he/she never again has to write specific references to the Objects.