What Do You Think About This?

[SamT]

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.

[SamT]

Enums are great. They work really well for column numbers. . . Until a User moves a column for their convenience.

Collections work well. . . As long as you initialize them every time the code runs after a Change.

Tables and List Objects are a decent replacement, but are sometimes difficult to grasp.

Named Ranges require complexity to make them dynamic, but they have the advantage of Excel keeping them up to date when layout changes.

What about:

'See post #1 this thread
Model = "ABC"
Qty = 300
Set Cel GetIntersect(Prices, Model, Qty)
Price = GetIntersect(Prices, Model, Qty).Value


Public Function GetInterSect(sht As Worksheet, ColAVal, Row1Val) As Range
Dim rw As Long
Dim col As Long

With Sht
rw = .Columns(1).Find(ColAVal).Row 'set Find Params as generic as possible
col = .Rows(1).Find(Row1Val).Column

If Not rw * col Then Exit Function

Set GetIntersect = .Cells(rw, col)
End With
End Function

[snb]

What is the benefit of adding 'objects' that are already unequivocally available everywhere in the VBProject ?

with application
with thisworkbook
with application.max( ... )

with sheet1 (or codename sh_orderentry )
with sheet2 (or codename sh_products )
with sheet3 (or codename sh_prices )

[SamT]

For you with your style? None at all.

[snb]

I meant in general..

[SamT]

I know.

For me in particular: If the Project references ThisWorkbook only 10 times, WkBk means 70 fewer chances for a typo. That sentence had five typos. I am a terrible keyboardist. The hardest words for me to finger correctly are work+sheet and work+book. I am pretty good with application.

In general, the obvious benefits are as mentioned in the first post.