Computers | A People for His Name

VBA Macro to Abbreviate State Names

April 5, 2024

While working on a project today, I ran into a problem with data in a spreadsheet that had the names of states fully spelled out instead of abbreviated. A brief search on the internet for a vba macro to do the task did not produce any results in the first page of hits.

Not finding such a macro, I decided to write my own using Select Case . . . End Select, and it worked perfectly!

VBA Macro to Abbreviate State Names

Sub AbbreviateStateNames()

‘Created by Rajesh Gandhi apeopleforhisname.org 4/5/2024

‘State names in column 5

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

lrow = ActiveSheet.Cells(Rows.Count,1).End(xlUp).Row ‘finds how many rows have data in the spreadsheet

For i = 2 To lrow

sn = Cells(i, 5).Value2

Select Case sn

Case “Alabama”
Cells(i, 5).Value2 = “AL”
Case “Alaska”
Cells(i, 5).Value2 = “AK”
Case “Arizona”
Cells(i, 5).Value2 = “AZ”
Case “Arkansas”
Cells(i, 5).Value2 = “AR”
Case “California”
Cells(i, 5).Value2 = “CA”
Case “Colorado”
Cells(i, 5).Value2 = “CO”
Case “Connecticut”
Cells(i, 5).Value2 = “CT”
Case “Delaware”
Cells(i, 5).Value2 = “DE”
Case “Florida”
Cells(i, 5).Value2 = “FL”
Case “Georgia”
Cells(i, 5).Value2 = “GA”
Case “Hawaii”
Cells(i, 5).Value2 = “HI”
Case “Idaho”
Cells(i, 5).Value2 = “ID”
Case “Illinois”
Cells(i, 5).Value2 = “IL”
Case “Indiana”
Cells(i, 5).Value2 = “IN”
Case “Iowa”
Cells(i, 5).Value2 = “IA”
Case “Kansas”
Cells(i, 5).Value2 = “KS”
Case “Kentucky”
Cells(i, 5).Value2 = “KY”
Case “Louisiana”
Cells(i, 5).Value2 = “LA”
Case “Maine”
Cells(i, 5).Value2 = “ME”
Case “Maryland”
Cells(i, 5).Value2 = “MD”
Case “Massachusetts”
Cells(i, 5).Value2 = “MA”
Case “Michigan”
Cells(i, 5).Value2 = “MI”
Case “Minnesota”
Cells(i, 5).Value2 = “MN”
Case “Mississippi”
Cells(i, 5).Value2 = “MS”
Case “Missouri”
Cells(i, 5).Value2 = “MO”
Case “Montana”
Cells(i, 5).Value2 = “MT”
Case “Nebraska”
Cells(i, 5).Value2 = “NE”
Case “Nevada”
Cells(i, 5).Value2 = “NV”
Case “New Hampshire”
Cells(i, 5).Value2 = “NH”
Case “New Jersey”
Cells(i, 5).Value2 = “NJ”
Case “New Mexico”
Cells(i, 5).Value2 = “NM”
Case “New York”
Cells(i, 5).Value2 = “NY”
Case “North Carolina”
Cells(i, 5).Value2 = “NC”
Case “North Dakota”
Cells(i, 5).Value2 = “ND”
Case “Ohio”
Cells(i, 5).Value2 = “OH”
Case “Oklahoma”
Cells(i, 5).Value2 = “OK”
Case “Oregon”
Cells(i, 5).Value2 = “OR”
Case “Pennsylvania”
Cells(i, 5).Value2 = “PA”
Case “Puerto Rico”
Cells(i, 5).Value2 = “PR”
Case “Rhode Island”
Cells(i, 5).Value2 = “RI”
Case “South Carolina”
Cells(i, 5).Value2 = “SC”
Case “South Dakota”
Cells(i, 5).Value2 = “SD”
Case “Tennessee”
Cells(i, 5).Value2 = “TN”
Case “Texas”
Cells(i, 5).Value2 = “TX”
Case “Utah”
Cells(i, 5).Value2 = “UT”
Case “Vermont”
Cells(i, 5).Value2 = “VT”
Case “Virginia”
Cells(i, 5).Value2 = “VA”
Case “Washington”
Cells(i, 5).Value2 = “WA”
Case “West Virginia”
Cells(i, 5).Value2 = “WV”
Case “Wisconsin”
Cells(i, 5).Value2 = “WI”
Case “Wyoming”
Cells(i, 5).Value2 = “WY”

End Select

Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

In Computers

VBA Word Macro for Adding a Blank Line after Each Verse in a List

February 4, 2024

Suppose you have used some kind of Bible software to export the text of a list of verses to a Word document. For some reason, the verses end up without a blank line in between them, and you would like to have a blank line in between all the verses.

The following macro adds a blank line after each verse in the Word document.

Sub BlankLineInsert()

‘Selects the whole document
Selection.WholeStory

‘Provides the total number of paragraphs
pp = Selection.Paragraphs.Count

‘Loops through the paragraphs and adds a line after each verse except the last verse

For i = 1 To pp * 2 – 2

‘Selects each paragraph
ActiveDocument.Paragraphs(i).Range.Select

‘Goes to the end of that paragraph
Selection.MoveRight Unit:=wdCharacter, Count:=1

‘Adds a blank line
Selection.TypeParagraph

‘Increases the value of i by 1 to account for the extra line that has been added
i = i + 1

Next i

End Sub

Note: This macro also works for any other Word document that is only text for which you want to add a blank line after each paragraph in the document.

In Computers

VBA Code to Sort Data of Varying Length!

October 27, 2023

While working on a work document, I recently figured out how to sort data of varying length (total number of rows) using VBA code. I arrived at the following code that works perfectly for my purposes for sorting data that is in 3 columns on the worksheet!

By first finding out how many rows are in column 1 (lrow), I was able to use lrow instead of a fixed number in the sort statements to get the code to automatically determine how many rows the data has and then sort it according to my specifications.

In Computers

GSE Search: Elohim Preceded by Plural Verbs with Exactly 3 Words Between

September 7, 2022

For a few weeks now, I have been intensely examining occurrences in the Hebrew OT of elohim with plural verbs. In the process, I have learned a lot about how to use the GSE in BibleWorks 10 to do Hebrew searches.

The following PDF shows what the search query is that finds all occurrences of elohim preceded by a plural verb form with exactly 3 words between:

GSE Elohim Plural Verb Preceding Exactly 3.

This query finds occurrences that occur after a 3rd person plural perfect, imperfect, waw-consecutive perfect, or waw-consecutive imperfect verb. It also finds occurrences after an active participle or a passive participle.

In Biblical Hebrew, Biblical Languages, Computers

Excellent Free Audio Transcription Software!

April 29, 2017

Based on information from a friend, I downloaded this morning an excellent free audio transcription program: Express Scribe!

I have several audio sermons that I would like to transcribe and found that this software makes the task a lot easier. I praise God for this free resource!

In Computers, Preaching

A New Resource for Learning Some Vocabulary of the Spanish New Testament

September 24, 2012

By using various capabilities of BibleWorks 7, Microsoft Office Word 2007, and Microsoft Excel 2007, I recently created a unique resource that should prove helpful for learning the Spanish vocabulary words with marked accents that occur 50 or more times in the Reina-Valera 1960 New Testament (R60NT).

Using BW7, I generated a list of the 25,733 words that occur in the R60NT and their frequencies. Pasting that list first into Word and then into Excel, I created a spreadsheet in Excel that listed all the words in one column and their frequencies in the next column.

Sorting that list by frequency, I produced a list of the 1186 words that occur 50 or more times. Using various macros that I created[i] in Microsoft Visual Basic 6.5 in Excel, I then identified the 259 words that have a marked accent on them.

Each word was then analyzed to determine the following:

—Length of the word

—Syllables in the word

—Diphthongs in the word

—Which vowel was accented and its exact location in the word

—Final letter, final two letters, final three letters, and final four letters of each word

By sorting this data, I produced the following listing of the words that categorizes and alphabetizes[ii] them by number of syllables, word length, accented syllable, vowel accented, and final letters (1-4, as applicable), respectively:

Analysis of Words with a Marked Accent That Occur 50 or More Times in the R60NT

One Syllable:

Letters	Words Alphabetically by Accented Syllable, Vowel, and Final Letters
2	dé sé mí sí tú; él
3	más

Two Syllables:

2	oí
3	acá noé iré qué así oyó; día mía oíd mío río oír aún
4	judá allá dará hará será dirá está daré haré seré josé esté allí leví sacó echó dejó tomó miró juró pasó mató cayó huyó jehú; cuál amán cuán joás amén país león amón saúl; días míos ríos sólo cómo; ésta éste
5	habrá podrá quedó mandó llegó habló halló llamó reinó entró sentó llevó; labán harán serán verán dirán basán están jamás demás harás serás dirás atrás rubén quién belén botín simón varón según algún jesús; dónde; ángel árbol éstas éstos
6	saldrá pondrá tendrá vendrá pondré; jordán moisés hebrón ningún; cárcel
7	tendrán vendrán pondrás tendrás

Three Syllables:

4	aquí esaú oído
5	caerá josué subió salió abrió hirió murió envió; había hacía decía tenía venía maría impío aarón; seáis elías caído; oídos
6	tomará estará lavará comerá morirá vivirá jehová traeré jericó durmió cubrió volvió habitó apartó; canaán caerán además efraín cabrío faraón simeón nación visión; hagáis estáis habéis sabéis tenéis haréis seréis habían decían tenían josías traído judíos impíos cuándo; líbano límite músico número; ídolos
7	llevará volverá enviaré neftalí entregó levantó comenzó; estarán comerán jonatán capitán satanás tomarás también manasés después porción reunión absalón salomón corazón; prójimo; cámaras cántico séptimo jóvenes púrpura; ángeles árboles
8	engendró preguntó destruyó; benjamín; tendréis; príncipe
9	destruiré; príncipes

Four Syllables:

6	isaías
7	edificó sucedió; alegría todavía oración
8	extendió; profecía posesión; comeréis jeremías zacarías; altísimo espíritu; imágenes ejército
9	levantará descendió respondió; jerusalén aflicción bendición expiación maldición salvación; levántate; ejércitos apóstoles
10	discípulos
11	destrucción

Five Syllables:

8	ezequías
9	aconteció; sabiduría; sedequías; acuérdate
10	generación; tabernáculo; primogénito
11	congregación
12	levantándose

Six Syllables:

11	abominación

I hope that this resource will help me and others to master the vocabulary of the R60NT and thereby in some way better minister to Spanish-speaking people!

[i] These macros are revisions of macros that I had originally created (with much help from my brother-in-law, Dr. Satya Narimetla, to whom I am very indebted for his help with that project) several years ago in a similar study of all the accented words in the Greek New Testament.

[ii] The words are alphabetized in this “reverse” way: by the last letter, last two letters, last three letters, and last four letters, respectively (as applicable), and then by the starting letter.

In Bible reading, Computers, Spanish

Answered Prayer – New Laptop!

October 14, 2011

The Lord recently blessed me with the first new laptop that I have ever had. My Samsung R540-JA08 has already been a tremendous help to me in many ways!

I look forward greatly to how this tool will help me to minister much more effectively in the years ahead, D.V.

Praise God for answering prayer!

In Computers, Prayer

Excel Sheet for Learning Multiplication Facts

October 4, 2011

This year, I have been tutoring two young home school students in Math and Science. Because they need help learning their math facts, I made a worksheet with macros in it in Excel that randomly gives them 100 multiplication problems at varying speeds.

The program first puts two numbers in the first two boxes and then shows the answer after a specified amount of time.

Here is a picture of the sheet:

The student can choose from four options:

Slower – gives them 3 seconds before showing the answer

Medium – two seconds before giving the answer

Harder – excludes the numbers 10 and 11

Instant – gives the answer at the same time as it puts the numbers up

Using this program, my goal is to get them to where they know well all their multiplication facts for 1 through 12 times 1 through 12.

Praise God for providing such useful features in Excel!

In Computers

Speed Listening and Reading the Bible

September 8, 2011

Today, I began working on increasing my reading speed. I decided to use a two-pronged approach.

First, using an audio MP3 CD with the KJV on it, I listened to Exodus 33-34 played by Windows Media Player on a high-speed setting of 2.000

while I also read the same chapters in my open Bible.

Second, I used a computer application that I made to speed read the same chapters (approximately 1800 words) at a speed of 663 words per minute.

At that speed, reading these chapters took me around 3 minutes to read.

Using this dual approach to read the Bible, my goal is to train both my eyes to read text faster and my ears to hear a faster rate of reading without any loss of comprehension in either way of receiving information. I hope to increase gradually my overall reading speed through this program so that I can read both important materials faster and less important materials even faster.

I also hope to speed listen to large sections of the Bible as a part of my exposing myself thoroughly to all the Scripture every year.

In Bible reading, Computers

Game Timer!

August 30, 2011

I like games like Pictionary but find using the hourglass timer frustrating because you always have to wait for it to completely shift the sand from one end to the other. To solve that problem, I made this simple game timer in Excel (with some help from my brother-in-law).

To use the timer:

In the cells next to the “Time Setting,” I set the time that I want to run the timer for (minutes, minutes and seconds, or seconds)
I click on the “Timer” button to load the time.
I click the “Timer” button to run the timer
I click the “Timer” button again to reset the time

Good stuff!

In Computers

Archives For Computers

VBA Macro to Abbreviate State Names

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: