File Size
is it possible that these user defined function could increase the file size? The end cell on all 5 worksheets refered to in the function return as their END CELL is: XFC1048576
Public Function funcInput(ByRef varRowNo As Variant, ByRef btyObjectNo As Byte, ByRef btyObjectColNo As Byte) As Variant
Application.Volatile True
On Error GoTo Errorhandler
Dim wksAssumptions As Worksheet
Select Case btyObjectColNo
Case Is = 1
Set wksAssumptions = wksAssumpObject1
Case Is = 2
Set wksAssumptions = wksAssumpObject2
Case Is = 3
Set wksAssumptions = wksAssumpObject3
Case Is = 4
Set wksAssumptions = wksAssumpObject4
Case Is = 5
Set wksAssumptions = wksAssumpObject5
End Select
funcInput = wksAssumptions.Cells(varRowNo, btyObjectNo + 4)
funcInput = wksAssumptions.Cells
Exit Function
Errorhandler:
funcInput = 0
End Function
Function funcIsFormula(c As Range) As Boolean
funcIsFormula = Left(c.Formula, 1) = "="
End Function
RE: File Size
Hi Varvara,
Thank you for the forum question.
I have no experience that user defined vba function should radically change the file size. I have experienced that they can slow down the file but that's all.
One problem I meet often is, that my clients store information in unused cell.
Try to select A1 on your sheets and press Ctrl End. Excel will now select the last cell in your sheets which will add to the file size. Use Clear All to remove information from "unused" cell. Save the file, Close and reopen.
You have a number of options to reduce the file size.
You can save the file as a binary file (xlsb). This will depending on the file reduce the size. If it is an old file (xls) the file will get smaller by saving it as (xlsm).
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
