Tracker
 ASP PHP JavaScripts MySQL MS Access MSSQL Subjects/Titles Post Text/Content
HOME    TRACKER    Visual Basic

# SumIf Function In VBA Code

## Edit by loquin: Original Thread was more than a year old. Please review the Posting Guidelines(PLUS, the answer IMMEDIATELY followed in post #2...) Quote:Originally Posted by GoodoldJackHi,I'm trying to write the equivalent to Excel's SUMIF funtion but in VBA code. I tried to rearrange the Excel funtion I initially typed in a recorded Macro but it doesn't seem to work for VBA.This is an example of what I'm trying to do. If Range("G1") = "" Then Range("J1") = "" Else Range("G1") = SumIf(Range("G:G"), Range("G1"), Range("I:I")) End IfI don't want the formula written into the cell in my Worksheet since I've got to many rows and it makes thw Worksheet extremely heavy.Thx,Jack Hi ............ I'm trying to solve the same problem. What was the answerThanks in advance

SumIf Function In VBA Code
Hi,
I'm trying to write the equivalent to Excel's SUMIF funtion but in VBA code. I tried to rearrange the Excel funtion I initially typed in a recorded Macro but it doesn't seem to work for VBA.

This is an example of what I'm trying to do.

If Range("G1") = "" Then
Range("J1") = ""
Else
Range("G1") = SumIf(Range("G:G"), Range("G1"), Range("I:I"))
End If

I don't want the formula written into the cell in my Worksheet since I've got to many rows and it makes thw Worksheet extremely heavy.

Thx,
Jack

Conditional SUMIF Function - Need Help
Hi,

After searched a function, I was not able to find a solution for my sheet.
It's nearly the same as SumIf function, to sum values from column A, but the criteria is to sum (column A) only with the first 3 values (=1) found in column B. It's easier to see example below to see what I mean. I've made a manual sum in column C.

___A___B__C
1__14__1__39 (C1=A1+A2+A6=14+11+14=39)
2__11__1__37 (C2=A2+A6+A9=11+14+12=37)
3__10__0__38 (C3=A6+A9+A12=14+12+12=38)
4__10__0__38 (C4=A6+A9+A12=14+12+12=38)
5__13__0__38 (C5=A6+A9+A12=14+12+12=38)
6__14__1__38 (C6=A6+A9+A12=14+12+12=38)
7__10__0__35 (C7=A9+A12+A13=12+12+11=35)
8__10__0
9__12__1
10_10__0
11_10__0
12_12__1
13_11__1

Do you know how to do implement the same function on column C that can be used in all lines?

thanks,

Alexandre

Multiple Criteria In Sumif Function
I am using an excel function worksheetfunction.sumif in a VB6 application. I want to sum values in a column of an excel sheet by using two sets of criteria.

for example,

column "C" should be summed only if the text in column "A" is "IP" and corresponding to this in column "B" the text is "Inforce".

Col A Col B Col C
IP Inforce 10
EP Inforce 20
IP Paidup 25
GB Inforce 35
IP Inforce 55

the code should return a sum of (10+55) = 65 ( first row and last row)

Any suggestions on how two sets of criteria and two sets of ranges can be given in a sumif function using VB6?

Sumif In Vab
I tried this code

Code:
Range("A4").Formula = "=SUMIF(N10:N65530, ,"George",R10:R65530)"It show
me this because of the string "George"
Compile error:
Expexted : end of statement

I tried another way by

Code:
Range("A4").Formula = "=SUMIF(N10:N65530, George,R10:R65530)"
But it doesn't work

How to fix this problem

Sumif Count If
Can anyone tell me how the sumif and countif functions work please? I have a spreadsheet with two named ranges. Range 1 is working fine with

=SUMIF(OFFSET(rngData,0,-1),C1,OFFSET(rngData,0,0))

=COUNTIF(OFFSET(rngData,0,-1),C1)

working brilliantly.
But range two says it has circular references and I am stuck.

=SUMIF(OFFSET(rngdata2,0,-1),F15,OFFSET(rngdata2,0,0))

=COUNTIF(OFFSET(rngdata2,0,-1),F15)

range 1 is in cols H & I
range 2 is in cols J & K

The data is reported in columns A & B for the first range and D & E for the second range (or at least I hope to).

If anyone can help the zip file is attached showing what I am trying to achieve. Sorry I am a total novice on these.

Sumif Between Two Dates
This may have been answered a million times, but I did a search and maybe I am not typing the right question, but I have a database and the user will be entering values for each day, however on the trends sheet I will break it down monthly so what I would need to do is have the formula or VBA code whichever is easier to look through all the dates and find the below criteria and sum up the column I choose(i.e. =04/01/06>05/01/06). I tried using SumIf, but it would not add up the data. =SUMIF('1st Shift'!A3:A20,"=04/01/06>05/01/06",'1st Shift'!C3:C20). Any help?

Sumif , Between To Sheets
tried to insert the Sumif function in sheet3's column2 to sum the cells in sheet2's column2, comparing with the match between the cells in sheet2's,column1 with sheet3's column1.
it's mean if data in sheet's2 column1 george,john,jeny,george,jeny - column2 ,2,4,5,4,8
and the data in sheet3's column1 has the same data .Sumif function will show the result (of sum the number in sheet2's column2) in the sheet3's column2 .

Code:
Private Sub CommandButton1_Click()
Dim cell As Range, cel As Range
Set myRange3 = Worksheets(2).Range("A1:A100")
Set myRange4 = Worksheets(2).Range("b1:b100")
For Each cell In myRange3
For Each cel In Sheets(3).Range("a1:a100")
If cel.Value = cell.Value And cell.Value <> "" Then
cel.Offset(0, 1).Value = "=SUMIF(Sheet2!A1:a100," & cell.Address & ",Sheet2!B1:b100)"
End If
Next
Next
End Sub
The Sumif doesn't work well .

SUMIF Criteria
Hi

Nice easy one for someone:

I have a spreadsheet which lists screws/nuts in stock and the quantity of each that is sent to different sites. I'm using the SumIf to count the running total number sent to each site

=SUMIF(B14:B299,"Watford",E14:E299)

I now need to seperate out the quantity of screw and nuts within this figure. How can I add a 2nd criteria to the formula?

Cheers

Sumif Problems
I am trying to use a sumif that will only add the points from column b if the date in column a is within the current month but all I get in return is 0. what am I doing wrong? Here is the fomula I am trying to use.

SUMIF(A:A,"current month",B:B)

Any help would be greatly appreciated.
Thanks

SUMIF Critera ??
Can the SUMIF "Critera" reference a cell in another worksheet? I have tried lots of different ways to do this but nothing works. I am trying to reference a cell formatted as currency.

Code:
=SUMIF(A1:A5,">Critera",A1:A5)

Thanks Bert

Complex SUMIF
I am trying to sum the values in a list, BUT I need to only sum those values where ID and Week match a certain value.

Sample Data:

Name IDHoursPatchDate Week
Test Name0005646523F2309/02/20045
.
.
.

Simple SUMIF:

Code:
=SUMIF(G3:G65536,5,D3:D65536)

I want to total Hours when, for example, Week = 5 and ID = 0005646

Any suggestions?
I've written a, very ugly and slow, vba solution but it's not what I want at all.

Thanks!

Countif/Sumif Conditional Help
I have some conditional formats in place that change the fill color in several columns. I am having difficulty trying to "countif" and "sumif" based on a fill color that results from a conditional format. Any help in this matter would be GREATLY appreciated.

SumIF Error 1004
Hi,
I am having a horrible time at tracking this down, but basically SumIf is not working within VB6.

I get Run-time error 1004
Unable to get the SumIF property of the WorksheetFunction Class

Code:
If sID = "050" Then
sTemp = "O" & iTop & ":O" & iBottom
dTemp = excel.WorksheetFunction.SumIf(wDataSource.Range(sTemp), ">0") ' Line that causes Runtime error 1004
.Cells(iCurrentRow, CI_StartColumnTotal).Value = dTemp 'WorksheetFunction.SumIf(wDataSource.Range(sTemp), ">0")
Else
.Cells(iCurrentRow, CI_StartColumnTotal).Value = WorksheetFunction.Sum(wDataSource.Range("O" & iTop & ":O" & iBottom))
'Sum works fine
End If
I am using VB6, and Excel 2K, and this is writing code inside a COM add-in for Excel.
Any help would be appreciated.

K

SUMIF Type Formula
What I would like to do is to get the sum from a range of cells for the cells whose month is a particular month in a formula, not in code. can someone help me out please?

Compound SumIf() Conditional Formula
I'm looking for what would be the equivelant to a compound SumIf() function that can be used in a formula.

For example:

The cells in a given Range (ie; E4:E25) can contain 1 of 3 different code values... "AB", "BW", and "RW"

The cells in a given Range (ie; G4:G25) can contain 1 of 4 different code values... "E5050NSTD", "F5050NSTD", "G5050NSTD", and "H5050NSTD"

The cells in another Range (ie; H4:H25) contain a numeric value.

If I wanted to calculate the sum of values from the Range H4:H25, whose corresponding E cells contained "AB", then I'd use the formula:

=SUMIF(E4:E25,"AB",H4:H25)

Likewise, if I wanted to calculate the sum of values from the Range H4:H25, whose corresponding G cells contained "F5050NSTD", then I'd use the formula:

=SUMIF(G4:G25,"F5050NSTD",H4:H25)

What I am having difficulty with, is creating a formula that would take two conditions to produce the sum of values from H4:H25.

In psuedo-code:

=SUMIF(E4:E25="AB" AND G4:G25="F5050NSTD",H4:H25)

Suggestions?

SUMIF Over Multiple Discrete Ranges
Hi All,

I have been having a few headaches trying to sort out a problem with SUMIF in my code (Excel 2000 VBA). After playing around for a while, I realised the range was a varying number of discrete groups of cells of varying size, which SUMIF isn't setup to handle.

While I found another way around the problem, I guess I'm just curious if there is a method of handling this inbuilt in VBA (for example array formulas), short of writing a UDF.

Sure, it wouldn't take long to write a UDF for it, but I am just wondering if this functionality is included as I imagine it would come up fairly regularly, and would be more efficient than a UDF.

Cheers,
Dangles

SUMIF Formula - Range To Vary
I need the sumif function to vary with the value of a month cell that i can select.

My formula (for January) looks something like:

Code:
= SUMIF (Sheets("A").Range("C"),"=Department A",Sheets("A").Range("D"))

For February, I would want it to sum Range("G") iso Range("D").

The trend in between months is +3 columns.

How can I put a variant into this formula?

Also, formula does not seem to work using Sheets(...).Range(...) kind of reference... How can I make it work?

Sumif With Source Data On Multiple Sheets
Hi everyone,
Been trying to use a sumif...

My workbook contains the following:

Evaluation range: B28:B38 on sheets 1:31
Criteria: cell value = certain text
Sum range: C7:C17 on sheets 1:31

What I tried to enter was "=sumif(1:31!B28:B38,"=text",1:31!C7:C17)" and what I get is "#value!". My ranges are probably incorrect, but can anyone tell me how to enter this, if it is possible at all to use sumif in this case?

Thnx.

SUMIF &"#VALUE&" If Looking In Another Closed Workbook
I have this formula in a Workbook in my worksheet BASE

=SUMIF('D:Promexma[tablacv.xls]TABLA1'!\$C\$1:\$C\$65535,BASE!E543,'D:Promexma[tablacv.xls]TABLA1'!\$G\$1:\$G\$65535)*F543

When tablacv.xls is closed it returns #VALUE

If I open the tablacv.xls then I get the calculated value... why? ... is there any way to correct this...

When the tablacv.xls is open this is how my formula is displayed...

=SUMIF([tablacv.xls]TABLA1!\$C\$1:\$C\$65535,BASE!E543,[tablacv.xls]TABLA1!\$G\$1:\$G\$65535)*F543

TIA

Get Code Function
Hi all,

i am trying to write a function that will bring back a code or string from a table using a where statement.
I want this to be used global, so i have wrote this code:

Code:
Function GetCode(stTable As String, stField As String, stDesc As String, stReturnField As String) As Long
Dim stRField As Field

stRField = stReturnField

SQL\$ = "SELECT * FROM " & stTable & " where " & stField & " = '" & stDesc & "'"
Set SQLRecs = db.OpenRecordset(SQL\$, dbOpenSnapshot)
With SQLRecs
GetCode = "!" & stRField
End With
Set SQLRecs = Nothing

End Function
However this code does not work, i cannot figure out how you convert a string into a table fieldname.

I am getting a mismatch when i try to set the stRField = stReturnField.

Can anyone help.

Thanks.

Code For A Function Key
I have a game without an autosave function, but while playing the game the key F12 brings up the save menu. What code do I use to simulate an F12 key press every 5 minutes (I know how to do the five minutes part!)

Thanks

Jason

How Do I Use This Code?? It Can't Function..
VB Code:

Hai i would like to ask how do i used this code?? I've got it from http://www.mentalis.org/apilist/WNet...nnection.shtml

It suppose to disconnect or connect the internet connection of a computer. But i can't get it to function. Can someone help me with this?? Please help...

Using A Function In My Code
I want to use the function shown on the folowing thread

the thing is where do i place this code for it to compile?

Eb

Vb Function Code
can a function in vb return a recordset

if yes then what is the syntax

i am working in sql server and vb.

How Can I Get The Name Of A Function Within The Code ?
Does anyone knows if it is possible to know the name of a function or a sub and the name of module or form within the module or the form itself ?
Maybe an example is better. If I use App.path I can get the full path of my application. It there something similar to get the name of the function (or subroutine) and the name of the module in which I am ?

TIA
Alberto Giordano

Mod 2 Function, Help With Code Please
Beginning programmer trying to write a program that will recieve a number input and determine if the number is odd or even. Here is the code that I have. It's probably all jacked up. Can anyone help?

Sub numberfinder()
Dim response As Integer
response = InputBox("Please enter a number between 1 and 100")
If response = Even Then
MsgBox ("Even")
Else
MsgBox ("Odd")

End If
End Sub

I know the if statement is wrong and that I need to use the Mod 2 statement but I am having trouble implementing it into the code.

What Different Between Sub And Function? And If I Want To Use Module How Code ?
i want every form call this function and now i using like this it work

Code:
Function GetCurrentPositions()
Dim intObjectNumber As Integer
For Each CurrentObject In Me
ReDim Preserve ObjectList(intObjectNumber)
With ObjectList(intObjectNumber)
.Name = CurrentObject
.Index = CurrentObject.TabIndex
.Left = CurrentObject.Left
.Top = CurrentObject.Top
.Width = CurrentObject.Width
.Height = CurrentObject.Height
End With
intObjectNumber = intObjectNumber + 1
Next CurrentObject

intReferenceHeight = Me.Height
intReferenceWidth = Me.Width

End Function

GetCurrentPositions
End Sub

Private Sub Form_Resize()
AutoScale
End Sub

Function AutoScale()

Dim dblXMultiplier As Double
Dim dblYMultiplier As Double
Dim intObjectNumber As Integer
Dim intFontSize As Integer

dblXMultiplier = Me.Height / intReferenceHeight
dblYMultiplier = Me.Width / intReferenceWidth

For intObjectNumber = 0 To UBound(ObjectList)
For Each CurrentObject In Me
If CurrentObject.TabIndex = ObjectList(intObjectNumber).Index Then
With CurrentObject
.Left = ObjectList(intObjectNumber).Left * dblYMultiplier
.Width = ObjectList(intObjectNumber).Width * dblYMultiplier
.Height = ObjectList(intObjectNumber).Height * dblXMultiplier
.Top = ObjectList(intObjectNumber).Top * dblXMultiplier
End With
End If
Next CurrentObject
Next intObjectNumber

If Int(dblXMultiplier) > 0 Then
intFontSize = Int(dblXMultiplier * 8)
lblFontResize.FontSize = intFontSize
txtSample1.FontSize = intFontSize
lblInstructions.FontSize = intFontSize
End If

End Function

in module

Code:
Public ObjectList() As ScreenObject
Public CurrentObject As Object
Public intReferenceHeight As Integer
Public intReferenceWidth As Integer

Type ScreenObject
Index As Integer
Name As String
Left As Integer
Top As Integer
Width As Integer
Height As Integer
End Type
but i want to move Function GetCurrentPositions() and Function AutoScale() to module for every form call same function how do i do ? how to chage ME to from name ?

Repeating Function But Not Code!
i have a text box where the user is going to input the number of times
and i have three options soc,sci,fun

when the user inputs 20 in the textbox,and selects sci form the options
20 items named "sci"will be added to list1

so list1 should look like this>

sci
sci
sci
sci
sci
...until there will be 20 sci

how can i do this simply,without repeating the code for each case until 20?

Equation Function In Code
Hi, I'm writing a simple program that shows strings in a lable baised on certain conditions.

Is there a way that I can insert a number with a super script that would apear like an exponent of the number?

I've used the Microsoft Equation 3.0 to write code in Word but I don't know of anything that would help me with this one. Any ideas?

Help Convert DAO Function(Code) To -&gt; ADO ?
hi guys,

im using this DAO Code

Quote:

VB Code:
Function NextJN()On Error Resume Next Dim NewNum As BooleanDim searchtr, lastnum, NextNum As StringDim X As Integer  NewNum = True rsLogbook.MoveLaststrJNum = rsLogbook.Fields("JNumber")lastnum = rsLogbook.Fields("JNumber"): lblJNumShow.Caption = rsLogbook.Fields("JNumber")lblJNum.Caption = Str(Val(lastnum) + 1)lblLastNum.Caption = lastnum NextNum = Trim(lblJNum.Caption) If Len(Trim(lblJNum.Caption)) < 5 Then        For X = 1 To (4 - Len(Trim(lblJNum.Caption)))           NextNum = "0" & NextNum         Next XNextNum = "SSS/" + Trim(NextNum) + "/" + Format(Date, "YY")End If strJobNumber = NextNum Exit FunctionEnd Function

can i how can i convert this one using ADO? thanks!

-

Need A Delete Function Code For Vb 6.0
i got this assigment to finish off and is asking me to write a code to add new menber and delete old members of the program how do i write the code???

Use Function From Database In Vb Code?
Hi everyone,
I have one question, how to do this?
First I'm declare two variables
dim x as integer
dim y as integer
x=6
y=3
In database in table 't1' there is a one field (string type) with value e.g. "(x*y)/(x+y)" or some other function.
Now, I want to read value from database into some variable
dim z as integer
z=t1.field (like I said value in that field is "(x*y)/(x+y)" )
and now value of variable z should be z=2. This value is result of function get from database:
z=(6*3)/(6+3)
I hope that my explanation isn't too confusing, and you realize what I want.

regard j

Code Of MOD Function Needed.
VB Code:
Dim a As Variant    Dim dummy As String    dummy = "7001480882"    a = dummy Mod 97

I've tried this but i get an overflow error on the function.
The mod function wants a long , and I am way over it.

Does anyone know how the syntax is from this function so i can rewrite it for usage without errors.

I am not sure it will work though cause the following line gives the same overflow error

VB Code:
Dim a As Variant  Dim dummy As String  dummy = "7001480882"  a = dummy i

Meanwhile I continue my search

How To Use Keyboard Function From Code?
I have created a form with command button as keyboard and text box as user input .I want to create a backspace key using command button, how to call the backspace function from the code?

Thanks.

Any Function Can Be Used To Set Code Page?
I have an XML file which contains some contents in Chinese, English and Portuguese. After my VB program read the contents from the file, I would like to do a preview in a PictureBox control. Although the contents are in UTF-8 format in VB internally, the contents in Portuguese or Chinese could not be shown correctly.

I think this problem is related to code page. Therefore, I would like to know if I can set the code page dynamically so that the PictureBox control can display the Portuguese or Chinese characters correctly. In Visual C++, there is a similar function. In Visual Basic, is there a similar one?

I Want To Cut My Code Parts And Put It Into A Function?
I have a long VBA code in a click event button. It want to use a Function rather then using having all the code in the click button. I want to implement a function were in the clickevent If status = true then run the function. My Function will contains an sql query but i am not too sure how to declare and pass the argument, and how do I call the function in the click event.

Here is the code I want to make into a function: I have two strSQL because I need to run the first query to run the second query
Code:
strSQL = "SELECT Last(tblExamDetails.examDate) AS LastOfexamDate, tblExamDetails.fkStudentID, tblExamDetails.fkPartID " & _
"FROM tblExamDetails " & _
"GROUP BY tblExamDetails.fkStudentID, tblExamDetails.fkPartID " & _
"HAVING ((tblExamDetails.fkStudentID)=" & Me.fkStudentID.Value & ");"

CurrentDb.QueryDefs("qryExamStatus").SQL = strSQL

strSQL = "SELECT qryExamStatus.*, tblExamDetails.Status " & _
"FROM qryExamStatus INNER JOIN tblExamDetails ON (qryExamStatus.fkPartID = tblExamDetails.fkPartID) " & _
"AND (qryExamStatus.fkStudentID = tblExamDetails.fkStudentID) " & _
"AND (qryExamStatus.LastOfexamDate = tblExamDetails.examDate);"

CurrentDb.QueryDefs("qryStatus").SQL = strSQL

Set rec = CurrentDb.OpenRecordset("qryStatus")

Do While Not rec.EOF

If (InStr(rec("Status"), "0") = 1) Then
statusOne = False
End If
rec.MoveNext
Loop

If statusOne = True Then
Me.Label12.Caption = "Overall Result = Passed"
Else
Me.Label12.Caption = "Overall Result = Failed"
End If

After running the code it displays the label "Overall Result =Passed" If statusOne = true else displays "Overall Result = Failed" if statusOne = false to the form.

Thanks,
Zub

Use Excel Function In Vb Code ?
I would like to use the excel function in my code.

Is't possible ? How can I call the function like average or slope, etc.

Thanks

Redg

How Do I Use The PtInRegion API Function?...heres My Code
Private Type COORD
X As Long
Y As Long
End Type

Private Declare Function CreatePolygonRgn Lib "gdi32" (lpPoint As Any, ByVal nCount As Long, ByVal nPolyFillMode As Long) As Long
Private Declare Function Polygon Lib "gdi32" (ByVal hdc As Long, lpPoint As Any, ByVal nCount As Long) As Long

Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As Long

Private Sub Form_Paint()

Dim poly(1 To 3) As COORD, NumCoords As Long, hRgn As Long

NumCoords = 3

poly(1).X = Form1.ScaleWidth / 2
poly(1).Y = Form1.ScaleHeight / 2
poly(2).X = Form1.ScaleWidth / 4
poly(2).Y = 3 * Form1.ScaleHeight / 4
poly(3).X = 3 * Form1.ScaleWidth / 4
poly(3).Y = 3 * Form1.ScaleHeight / 4

Polygon Me.hdc, poly(1), NumCoords

End Sub

-------------------

ok i got this code from a web page and made it shorter so i could understand it, now i want to detect if a point is inside the region that i just made above...

i tried getting examples off the net but they were to complicated

so could someone write me some code on how to use the point in region command?

Invoking A Function In Excel+VBA Code From A DLL
Hi,

I need to invoke a VBA function from a dll.

My scenario is as follows.

I'm working with Excel and have a Smart Tag menu(my own written in a dll). I want to invoke a VBA function when the user clicks on any of the menus.

Is this possible ?? If yes then pl. help.

Regards,

Sudarsan.

Replace Function, ASCII Code
Hi,

I read a Word document and put it content in a string. It works. Problem is that there's table in the word document so the string is filled with the square character(ٱ), delimiting every row of the table. The string looks like these :

ٱٱٱٱٱٱٱBlahblahٱBlahblahٱBlahblahٱٱٱٱٱٱٱ

I tried to use replace to replace the square but when i copy/paste it from Word it pus a ? instead of ٱ in the VB code. So i tried to replace it by the ascii code but never works:

strW = Replace(strW, Chr(141), "")

Need some help

Thanks

Call Vb6 Function From Script Code
Is there a way to call client function from script code.
I have to invoke a function from Jscript in the web page.
Using vb.net i's not problem but is there an equivalent of webBrowser1.ObjectForScripting in vb6?

hey everyone, this is my first post and my first time uing vb, so please can you be very descriptive in any help you give. the problem is that i want this function to return the formatted post code if the postcode is correct, but it just returns true if it is and false if its not. i need the formatted post code so that i can put it back into the textbox that i am taking the original post code out of. i will post the function code, but if you need the whole code just say.
thankyou in advance everyone who helps

Joe

Code

Function Check_Postcode(ByRef strPostcode)

Dim strPostcodeRegExp(5) ' holds the regular expressions for valid postcodes
Dim intCount ' For loop counter
Dim strPostcodeCopy ' Copy of postcode

' Variables used to hold regular expression object
Dim objRegExp, objMatches, objMatch

' Variables to hold list of valid letterrs for various parts of the post code.
Dim strAlpha1, strAlpha2, strAlpha3, strAlpha4, strAlpha5

' Set up letters valid in thge various postcode positions
strAlpha1 = "[abcdefghijklmnoprstuwyz]" ' Character 1
strAlpha2 = "[abcdefghklmnopqrstuvwxy]" ' Character 2
strAlpha3 = "[abcdefghjkstuw]" ' Character 3
strAlpha4 = "[abehmnprvwxy]" ' Character 4
strAlpha5 = "[abdefghjlnpqrstuwxyz]" ' Character 5

' Expression for postcodes: AN NAA, ANN NAA, AAN NAA, and AANN NAA
strPostcodeRegExp(0) = "^(" + strAlpha1 + "{1}" + strAlpha2 + "{0,1}[0-9]{1,2})([0-9]{1}" + strAlpha5 + "{2})\$"

' Expression for postcodes: ANA NAA
strPostcodeRegExp(1) = "^(" + strAlpha1 + "{1}[0-9]{1}" + strAlpha3 + "{1})([0-9]{1}" + strAlpha5 + "{2})\$"

' Expression for postcodes: AANA NAA
strPostcodeRegExp(2) = "^(" + strAlpha1 + "{1}" + strAlpha2 + "[0-9]{1}" + strAlpha4 + "{1})([0-9]{1}" + strAlpha5 + "{2})\$"

' Exception for the special postcode GIR 0AA
strPostcodeRegExp(3) = "^(gir)(0aa)\$"

' Standard BFPO numbers
strPostcodeRegExp(4) = "^(bfpo)([0-9]{1,4})\$"

' c/o BFPO numbers
strPostcodeRegExp(5) = "^(bfpo)(c/o[0-9]{1,3})\$"

' Copy the parameter and convert into lowercase
strPostcodeCopy = LCase(strPostcode)

' Assume we're not going to find a valid postcode
Check_Postcode = False

' Strip out spaces
strPostcodeCopy = Replace(strPostcodeCopy, " ", "")
Check_Postcode = False

Set objRegExp = New regexp

' Check the string against valid types of post codes
For intCount = 0 To UBound(strPostcodeRegExp)

' Check next pattern in list
objRegExp.Pattern = strPostcodeRegExp(intCount)
If objRegExp.Test(strPostcodeCopy) Then

' Post code found. Ensure input parameter is in correct format.
Set objMatches = objRegExp.Execute(strPostcodeCopy)
Set objMatch = objMatches(0)
strPostcodeCopy = UCase(objMatch.subMatches(0)) & " " & UCase(objMatch.subMatches(1))

' Take account of the special BFPO c/o format
strPostcodeCopy = Replace(strPostcodeCopy, "C/O", "c/o ")

' Show that we have found the postcode
Check_Postcode = True
End If
Next

' Ensure that the uppercase postcode gets returned if valid
If Check_Postcode Then strPostcode = strPostcodeCopy

End Function

API Function Taking Precedence Over My Code?
I found some code that allows you to ping a host and decided to implement it. I based my own program on the example found here: http://www.freevbcode.com/ShowCode.asp?ID=4945

My code looks like this, schematically:

Clear a textbox
Call sub Ping

-Sub Ping-
Print text to richtextbox
Call function SimplePing four times
print text to richtextbox

-Function SimplePing-
'Basically the same as in the example, except I have it print to the richtextbox a couple of times

Now, here's my problem. Instead of the events actually taking place in the order I want them to, once I fire this chain of events, my program executes the API functions four times and only prints to the richtextbox when it is done, causing all the text to appear at once, which is not how I intended it to work. Could anyone tell me why this happens and how I could make things happen as I want them to?

Need The Code To Call A Function After Every 10 Seconds In Vb6
guys,

i need to execute a function every 10 seconds for my entire application, please help me, or i f u have a code that would be appreciated/////

Easier Way Of Make Code For Function?
Is there an easier way to do this?

VB Code:
Public ComputerStrategy() As Long     If lblBox5.Caption = "" Then        lblBox5.Caption = "O"        ElseIf lblBox1.Caption = "" Then        lblBox1.Caption = "O"            ElseIf lblBox9.Caption = "" Then        lblBox9.Caption = "O"            End If    End Function

That's not all my logic, my program is at school and I forgot to e-mail it here, so I just typed up a few I remember. It's for a tic tac toe game, easier way to do it???

To Find The Code Corresponding To An Inbuilt Function
Hello
How do i find out the code to an inbuilt function
say Printer.EndDoc

What Code Is Behind The Slow DoEvents Function?
Yeah. What code is behind the slow DoEvents function? C++ uses PeekMessage which is much faster than DoEvents. I was curious on what code Microsoft used in that function. I have already found ways to tame DoEvents as well as do faster methods. Even one that is 10 times faster than DoEvents alone.

So why is DoEvents slow anyways? I'm curious.

Can Anybody Duplicate The Code For The MAX Function In VBA For Excel?
I need to duplicate the MAX function. The function must pass in a range of cells like so:

MAX(A1:A4)

Function Keys Ex: F8, F4, Etc &amp; Ansi Code
Hi All,

Just printed the character set 0-127 and 128-255 from the VB Help file. I was looking for the numerical values which represent function keys.

I know one of you who is reading this now has the answer.

So start talkin' err typing!

Thanks,
Sal