Using VBA to replace thousands of INDIRECT functions

[speedygib]

Looking for some help here - I am trying to reduce the file size of an excel file I use on a quarterly basis. The file was initially 18,000 KB and I've gotten it down to 10,000 KB but I think there is still room to improve.
My file has 145 sheets. 12 of those sheets use the INDIRECT function paired with HLOOKUP to pull data from 100 individual sheets that contain information relating to specific investments over a period of time. Does anyone know if it would be faster (and if my file size would get smaller) if I replace these functions with VBA code? I've only used VBA a few times so I would need specific instructions on how to implement this.
Here's an example of one of the functions:
= IFERROR(HLOOKUP(L$9,INDIRECT("'"&$B18&"'!"&$B$3),14,FALSE)/1000,0)
Where:

1. L9 contains a date that i am looking up in the array on the specific investment tab sheet
2. Column B contains the name of the individual tabs (i.e. B18 is "GOX" and i have a separate tab named GOX where i'm pulling data from)
3. B3 contains $B$11:$DFR$24, which is the array I am searching on the specific investment tab
4. and row 14 is the row I am pulling from the array (so it's technically row 24 in the sheet i'm pulling from, but row 14 from the array i'm looking up within that sheet)

Relevant Info: Excel version 2016, on a PC desktop, Beginner to Intermediate Excel Knowledge.

 

[offthelip]

Welcome to the forum. The answer is yes using vba is very likely to reduce the size of your file and make it much much faster. The really fast way of doing lookup with vba is to usethe dictionary object.
I have written some code from the limited example you gave, exactly what the code is depends on how the best way of replacing all of the lookup is:

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   wsname = Range("B18")
   wsrange = Range("b3")
 
   With Worksheets(wsname)
   Set Dic = CreateObject("Scripting.dictionary")
      Ary = .Range(wsrange).Value2
   End With
   For i = 1 To UBound(Ary,2)
      Dic(Ary( 1,i)) = Ary( 14,i)   ' this is the row number of the array
   Next i
 
 
   With Worksheets("sheet1")  ' I don't know where you want the answer
    ' it quite common to have a number of lookup to do for eahc sheet this is why I have put a loop in here I assume you want answers on every row in columnn L
       For Each Cl In .Range("L2", .Range("L" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = Dic(Cl.Value) 'this matches the value given by tyhe index C1.value in the dictionary
      Next Cl
   End With
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Using VBA to replace thousands of INDIRECT functions
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.