VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

Leaving sensitive information (like passwords) in clear text scripts is never a good practice, though it's sometimes unavoidable. This set of VBScript functions can be used to obscure critical information making it at least a little more difficult for curious eyes (or worse) to see.
0
 
LVL 43

Expert Comment

by:Rob
Comment Utility
Hi Lee,

I really like this script as I've had to use a lot of vbs for automating systems and interacting with databases and reports and this would be really useful.

I did get an error with the Decrypt part when using a 10-10-10 key.  Is there a limitation to the size of the key? what am I missing?  The following did work when I used a smaller key e.g. 7-7-7

Error:
C:\Users\rjurd\OneDrive\Desktop>encrypt.vbs E monsterAiden18 10-10-10
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

674A748B36662E6DB5860512A66C8698F6E0B910238EF143C1E206F731E7AAAC23757697449AB80B982D89F89DBE77B73B22B555A67FAA4B9EE634B30F6A

C:\Users\rjurd\OneDrive\Desktop>encrypt.vbs D 674A748B36662E6DB5860512A66C8698F6E0B910238EF143C1E206F731E7AAAC23757697449AB80B982D89F89DBE77B73B22B555A67FAA4B9EE634B30F6A 10-10-10
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

C:\Users\rjurd\OneDrive\Desktop\encrypt.vbs(43, 9) Microsoft VBScript runtime error: Invalid procedure call or argument: 'Chr'

Open in new window

0
 
LVL 97

Author Comment

by:Lee W, MVP
Comment Utility
@Rob, I'm glad you find it useful!  Thanks to your comment I realized I forgot to mention a key point (no pun intended).  The last digit cannot be greater than 9.  I've also detailed how the hashes are made up, hopefully a little more clearly than following the code.  This is by design.

@everyone else, if you have a problem like Rob did, please let me know.  I'd like to perfect this as much as I can perfect it. I'm also open to tips to make it stronger (already have one idea).
1
Free Tool: ZipGrep
LVL 12
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

It is not that simple from VBA to display a compressed help file as it should be. API calls and a few tricks are needed. This demo and full code will show you how.
0
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
0
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
0
Multi-Purpose Knife
This article presents several of my favorite code snippets.
2
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
0
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
0
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
0
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
0
Microsoft Access
Implementing simple internal controls in the Microsoft Access application.
0
 
LVL 54

Expert Comment

by:Gustav Brock
Comment Utility
Please edit the article to hold the code in code blocks with normal indention to make it readable and allow for copy-paste.
0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, this article addresses the 'improvement'...
0
Print Preview
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
0
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
0
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
0
Unlock VBA Project Password Excel File
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
0
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
1
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
0
 
LVL 1

Author Comment

by:Shai Alon
Comment Utility
You're right, and I'm sorry.
I was frustrated from that post that it bothered me that the answer seemed simple.
I've modified the post to be neutral and answer the question only.

Regards,
Shai
0
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
3
Warning
This article describes a serious pitfall that can happen when deleting shapes using VBA.
3
The 14th Annual Expert Award Winners
LVL 7
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Windows Phone Theme Colours
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
3
 

Expert Comment

by:John Clark
Comment Utility
Gustav,

I adapted my application to this and I could not believe how efficient and easy this was to do. I see myself deleting a HUGE amount of redundant code and using Class Module in the future. They have always intimidated me and quite frankly, I did not see the use of them until I needed something similar to what you did here. The link you provided to John Colby's Blog was spot-on and VERY informative.

A question on this method however, the particular functionality I am trying to achieve applies to Text and Combo boxes. Reading JC's Blog led me to believe that I would need another Class to handle each type of Object.

Did I comprehend that correctly or can I include more than one Object in this Class?

Great stuff!

John
0
 
LVL 54

Author Comment

by:Gustav Brock
Comment Utility
Yes, you implement many classes, perhaps one for each type of control.

Or you can modify this check:

    If Control.ControlType = acTextBox Then

to check for other control types as well. And then either apply the same or different methods.

Or do both. Or add more conditions; for example only apply a method to a control with a control source.

/gustav
0
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
0
Decision Making
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
28
 

Expert Comment

by:Brendan Wilson
Comment Utility
This is an excellent article. I appreciate you taking the time to write this.
1
 

Expert Comment

by:Judy Deo
Comment Utility
Thanks for taking the time to post this in such detail. Appreciate it.
1
Security
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
2
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
1
 
LVL 50

Expert Comment

by:Dale Fye
Comment Utility
Jim,

Great article.  I use a similar process in a procedure I use for reading from or writing to Excel.  The problem is that occassionally, the user might want to read from or write to a workbook which is already open, and if the user has more than one instances of Excel open it is vertually impossible to select the correct version.  With a method very similar to this, I am able to identify all available instances of Excel and allow the user to select the appropriate instance based on the workbook(s) opened in those instances.

Dale
0
Screenshot of Automated Link Checker
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
1

VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.