Vba screenupdating word

13 Jan

Please find the following statements for better understand. Enable Events = False at the beginning of your code and Application. Use ‘WITH’ statement when working with Objects in macro. So always better to use vb Null String instead of “”.

I would not recommend doing this for more than 100 accounts per worksheet as it effectively performs one SQL query per function (100 queries) and be slow to refresh.

But for multi-company dashboards (such as inter-company balance checks) or simple balance sheets, pulling account balances is quick and painless.

Screen Updating = True End Sub Sub Act X_Add_Multiple_Check Box_Ex2() 'Disable Screen Update Application.

Valli is sharing useful VBA examples ad Tips to helps us automating daily tasks.

So that Excel does not calculate values in the workbook cells.It will help you to prevent or stop endless loops while executing or running macro if you have worksheet or workbook events. Pattern Tint And Shade = 0 End With End Sub If you compare recorded macro and written macro so much of difference will be there. It occupies less memory compares to “” and faster in process and to assign.While this may not apply to all companies, we are license starved at my company so everyone has to fight to log into GP.This effectively bypasses the need to log into GP to pull a simple balance as this queries the SQL Server directly. Enable Events = False Dim query As String Dim server Var As String If Is Missing(server) Then server Var = gp Server Else server Var = server End If query = “select sum(PERDBLNC) ” & _ ” from (select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10110″ & _ ” Union all” & _ ” select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10111 ) GL” & _ ” where” & _ ” actindx in (select distinct actindx from GL00105 where actnumst = ‘” & account & “‘)” & _ ” and year1 = ” & fiscal_year & _ ” and periodid Enjoy, ~James Disclaimer: I am very much an amateur programmer. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set.Screenshots GPAcc Bal VBA Code: Public Const gp Server As String = “” ‘Put your SQL Server name here Function GPAcc Bal(company, account, period, fiscal_year, Optional server) ‘Do not forget to import Active X library ‘ ‘References : Microsoft Active X Data Objects 6.1 Library (or newest version) ‘Written By : James Lyn ‘Date Written : December 2014 ‘Purpose : To return period balance for one account from the GP database ‘ ‘Inputs ‘ company : designates the database to query against ‘ account : designates the account number to query ‘ period : 1-12 – designates the period for which to return the balance as per the fiscal calander ‘ year : i.e. Enable Events ‘Disable unnecessary functionality Application. By using my files you agree to not hold me liable for any damages caused by said files.2014 – deisgnates the fiscal year to query ‘ server : optional, default gp Server.

vba screenupdating word-2vba screenupdating word-73vba screenupdating word-25vba screenupdating word-54