Programming Skills: Some good sites to explore and learn VBA - Excel, Word, Outlook, Access & PowerPoint

Microsoft Office Programming Skills

Microsoft Office applications such as Word, Excel, PowerPoint, Outlook and Access can be controlled in several aspects of user interface (UI) including menus and toolbars. Looking at the benefits of VBA which doesn’t need any additional software to be installed other than Microsoft Office, many will definitely agree to learn it. It doesn’t matter if you are new to VBA or have any basic programming knowledge, once you start, it will be a lot easier.

Technically, in brief, Visual Basic for Applications (VBA) is closely related to Microsoft Visual Basic 6.0 programming language. Through VBA, Microsoft Office applications features can be customized and automated. Like Visual Basic 6.0, VBA is also event-driven and references Visual Basic Runtime Library. Using Windows API functions and dynamic-link libraries (DLLs), VBA programmer can create their own functionalities within Office applications. OLE Automation allows VBA to interact from within one Office application to other.

You can find some good sites online which provide free VBA tutorials along with good examples. You can start with Office application you use most. For e.g. VBA for Microsoft Excel and then later on others. You can refer below mentioned sites which looks to be good source for learning VBA.

Microsoft Excel VBA learning sites:

Chip Pearson and Pearson Software Consulting, LLC

Ozgrid

Microsoft MSDN library for Excel VBA

Excelfunctions.net

Excel-vba.com

Chandoo.org

Microsoft Word VBA learning sites:

Shauna Kelly

Microsoft MSDN library for Word VBA

Bright Hub

TechRepublic

Microsoft Outlook VBA learning sites:

Microsoft MSDN library for Outlook VBA

OutlookCode

JP Software Technologies

Microsoft Access VBA learning sites:

Microsoft MSDN library for Access VBA

Microsoft Access Tips blog

FunctionX

Infinite Skills

Microsoft PowerPoint VBA learning sites:

Microsoft MSDN library for PowerPoint VBA

Basic VBA for PowerPoint

Indezine

eHow

Articlesbase

FileMaker Pro: Communication with the host was interrupted and could not be re-established. All affected windows will be closed.

FileMaker Pro sudden disconnection issue

Sometime FileMaker Pro (FMP) users face sudden disconnection of FMP client. Before disconnection, the FMP client pops up an error message – “Communication with the host was interrupted and could not be re-established. All affected windows will be closed”. This might happen multiple times a day.

This issue can occur for FMP users working in an environment where network connectivity fluctuates or connection speed is very slow.

If this issue occurs only for a particular user and other users at same location have no such problem, below steps should be tried for that user:

• Request the user to restart the machine and retry
• If the issue still persist and there is no network connectivity problem going on, uninstall FMP client on user machine and then again do fresh install.

If this issue is common for most of the users in any particular network or location, the network performance play an important role in this situation. Networking team should be involved to address the connectivity slowness issue and request them to look into optimizing network performance for FMP shared database files. Minor network fluctuation can disconnect the FMP client which in turn close down all opened FMP databases immediately on user side. Due to this, there are high chances of losing unsaved data. Sometime FMP database file might get corrupt also.

By the time network problem is not resolved, below workaround can be tried. These steps can minimize the chances of sudden disconnection of FMP client and data loss.

• Users should try to work on minimum FMP database file/screen at one time. They should close FMP layouts or screens which are not needed at that moment. This will keep minimum network traffic between FMP client and FMP server.
• Implement “Flush Cache to Disk” script step and allow users to use it frequently to avoid loss of cached data on their end.
• On FMP client side, keep the option “Save cache contents” to every 10 min to save the data to FMP Server disk every 10 min
• On FMP Server side, the option “Set maximum idle time allowed for FMP clients” can be unchecked through FileMaker Server Admin Console to make sure that FMP clients are not disconnected by that time constraint configuration setting. This option you can find in FileMaker Server Overview à Configuration à Database Server à FileMaker Pro clients à Client settings.
• FMP script which takes long time to execute should be optimized to work faster.

FileMaker database file could not be opened. Either the host is not available, or the file is not available on that host.

FileMaker database

Sometime user(s) face this error message while connecting FileMaker Pro (FMP) database files hosted on a particular FileMaker Server (FMS). Following things need to be ensured before checking the type of user account.

• Make sure that FMP Server is up and running without any performance issue
• Make sure all needed FMP database files are enabled i.e. in “Normal” state

If above points are already taken care of and still the issue persist, check the FMP account type and access of the user.

If user is of FileMaker account type, then make sure that user has appropriate permissions and user is using correct login credentials.

If user is of External Server accounts type, then make sure that user NT id has been added to appropriate  Active Directory security group. If all External server account user are getting similar error message, then follow below steps to make sure proper FMP client authentication settings for that host.

• Login FMP Server
• Login FileMaker Server Admin Console
• Go to FileMaker Server Overview à Configurationà Database Server. In Security tab, make sure that for client authentication “FileMaker and external server accounts” option is selected. If not, select this option to allow all FMP users to login either of FileMaker accounts or External Server accounts.

VBA: Proactively upgrade Excel Add-ins and Macros to avoid unnecessary errors while working with new Office version

Upgrade Excel Add-ins and Macros

With every new version of Microsoft Office, user machines are upgraded with latest Office version for mostly following reasons:

·         To utilize benefits of new functional and technical features

·         The upgrade might be followed as part of company policy

·         To avoid any sort of compliance issues

·         To overcome the limitation or constraints present in earlier version

·         To meet demands of internal and external clients

Reason may be multiple, however Microsoft Office based applications are affected once Office version changes on user machine. VBA applications need to be upgraded to avoid future warnings and/or errors within the application. When we talk about VBA based application, multiple things pop up in our mind.

·         VBA dynamic link libraries  backward compatibility

·         VBA template changes like design, formatting etc.

·         VBA add-in changes

·         Application SMS package change

·         Digital signing of VBA templates and add-ins

If VBA based applications like Excel add-ins, macros and templates are not aligned with the latest Office version, following situation might occur.

·         User’s queries and escalations resulting due to new warnings or errors in VBA application. Most of the time there is no error but business users consider new alert or warning, as change in application behavior. If it is proactively communicated by product management group, it might reduce user frustration.

·         If VBA application is continued on old Office version, it might introduce additional management approvals to support the application on old Office version and thereby more delay for application SMS packaging group to work on software packaging and SMS testing. This delay is introduced due to absence of old Office version on their machines needed for VBA templates and add-in digital signing and unit testing with old Office version.

·         VBA application on old version might work due to backward compatibility supported by new Office version but the application might perform slow or hang at some point of time. It might face unexpected errors as well. Design and formatting of document might appear disturbed and different in some cases.

There can be more technical and functional issues with VBA applications working with older Office version. Hence it is recommended to upgrade VBA applications depending on change in Microsoft Office version.