Serving the Quantitative Finance Community

 
User avatar
daveangel
Topic Author
Posts: 5
Joined: October 20th, 2003, 4:05 pm

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 11:28 am

I have a fairly large number of VBA modules and I have noticed that the editor will sometimes and without warning capitalise some of the variable names. Anyone else seen this ? Is there a way to get VBA to stop this ?
knowledge comes, wisdom lingers
 
User avatar
Rufus
Posts: 4
Joined: January 18th, 2002, 5:24 pm

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 12:46 pm

** VBA voodoo alert, what follows may be witchcraft *** Try opening your spreadsheet macros disabled, saving it and then re-opening macros enabled. This sorts out all sorts of excel oddities and also often reduces the file size as a side effect.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 1:08 pm

That happens, whatever the number of modules.That is sometimes helpful, sometimes not.
 
User avatar
daveangel
Topic Author
Posts: 5
Joined: October 20th, 2003, 4:05 pm

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 1:19 pm

let me be clear about what is happening just in case it is not.Lets say I have written a variable spelled as "niter" and that I have used this consistently everywhere. At some other point in time when I re-open the module, this can be spelled NITER without me changing anything.
knowledge comes, wisdom lingers
 
User avatar
Rufus
Posts: 4
Joined: January 18th, 2002, 5:24 pm

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 2:36 pm

I have seen this but I never found a good solution to it. There's some advice here:stackoverflowBeen a while since I coded in vba, but I remember we used to use this on the codebase from time to time: code cleaner(I think this automates the close down and open macros disabled trick). Alternatively - could you switch to C# and use ExcelDna or C++?
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

VBA editor unrequited capitalisation of variable names

April 9th, 2013, 6:00 pm

QuoteAlternatively - could you switch to C# and use ExcelDna or C++?ExcelDna very user-friendly.
 
User avatar
MaxCohen
Posts: 0
Joined: June 13th, 2007, 2:44 pm

VBA editor unrequited capitalisation of variable names

April 10th, 2013, 10:45 am

Are you using 'Option Explict' in each module/class/form?If you not then you can not complain if crazy stuff happens. Rule one of VBA is always use 'Option Explicit'! Also ExcelDNA is most probably overkill for most tasks. VBA is more stable and transparent and underrated as a programming tool. ExcelDNA should be a last resort and is certainly not a RAD tool.
Last edited by MaxCohen on April 9th, 2013, 10:00 pm, edited 1 time in total.
 
User avatar
daveangel
Topic Author
Posts: 5
Joined: October 20th, 2003, 4:05 pm

VBA editor unrequited capitalisation of variable names

April 10th, 2013, 2:08 pm

QuoteOriginally posted by: MaxCohenAre you using 'Option Explict' in each module/class/form?If you not then you can not complain if crazy stuff happens. Rule one of VBA is always use 'Option Explicit'! Also ExcelDNA is most probably overkill for most tasks. VBA is more stable and transparent and underrated as a programming tool. ExcelDNA should be a last resort and is certainly not a RAD tool.I am religious about explicit declarations and use of option explicit
knowledge comes, wisdom lingers
 
User avatar
MaxCohen
Posts: 0
Joined: June 13th, 2007, 2:44 pm

VBA editor unrequited capitalisation of variable names

April 10th, 2013, 2:36 pm

What is the scope of variable 'niter'? Is its scope to the project, module or procedure?
 
User avatar
daveangel
Topic Author
Posts: 5
Joined: October 20th, 2003, 4:05 pm

VBA editor unrequited capitalisation of variable names

April 10th, 2013, 2:45 pm

QuoteOriginally posted by: MaxCohenWhat is the scope of variable 'niter'? Is its scope to the project, module or procedure?niter is scoped at procedure level. rarely (if ever) use project or module scope.
knowledge comes, wisdom lingers
 
User avatar
MaxCohen
Posts: 0
Joined: June 13th, 2007, 2:44 pm

VBA editor unrequited capitalisation of variable names

April 10th, 2013, 3:12 pm

I have been told it is a procedure thing. At some point somebody has declared NITER as well as niter.E.g. There are 2 modules. Module 1 originally used NITER and module 2 originally used niter. Depending on how the project is opened will depend on how the variable is shown.