98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » File Size
File Size
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Removing the Ribbon from view in Excel 2010At 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. |