file size

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » File Size

File Size

resolvedResolved · Urgent Priority · Version 2010

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

Fri 8 Feb 2019: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Removing the Ribbon from view in Excel 2010

At times when you want to view the whole spreadsheet, try double clicking on the ''Home'' tab on the ribbon which will hide the ribbon from view.

View all Excel hints and tips


Server loaded in 0.08 secs.