File Size

Resolved · Urgent Priority · Version 2010

Fri 1 Feb 2019, 14:02 Varvara (guest) said...

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

Sat 2 Feb 2019, 09:46 Trainer Jens said...

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