Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  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...)


Originally Posted by GoodoldJack

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") = ""
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.


Hi ............ I'm trying to solve the same problem. What was the answer

Thanks in advance

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
SumIf Function In VBA Code
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") = ""
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.


Conditional SUMIF Function - Need Help

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.

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)

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



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

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

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



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



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 .

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
End Sub
The Sumif doesn't work well .

SUMIF Criteria

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


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?


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.

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.


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:


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.


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
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

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")
.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.


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:


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:


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)


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.


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:

= 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?


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


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...



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:

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.


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!)



How Do I Use This Code?? It Can't Function..
VB Code:
Private Declare Function WNetAddConnection Lib "mpr.dll" Alias "WNetAddConnectionA" (ByVal lpszNetPath As String, ByVal lpszPassword As String, ByVal lpszLocalName As String) As LongPrivate Declare Function WNetCancelConnection Lib "mpr.dll" Alias "WNetCancelConnectionA" (ByVal lpszName As String, ByVal bForce As Long) As LongConst WN_SUCCESS = 0 ' The function was successful.Const WN_NET_ERROR = 2 ' An error occurred on the network.Const WN_BAD_PASSWORD = 6 ' The password was invalid.Function AddConnection(MyShareName As String, MyPWD As String, UseLetter As String) As Integer    On Local Error GoTo AddConnection_Err    AddConnection = WNetAddConnection(MyShareName, MyPWD, UseLetter)AddConnection_End:    Exit FunctionAddConnection_Err:    AddConnection = Err    MsgBox Error$    Resume AddConnection_EndEnd FunctionFunction CancelConnection(DriveLetter As String, Force As Integer) As Integer    On Local Error GoTo CancelConnection_Err    CancelConnection = WNetCancelConnection(DriveLetter, Force)CancelConnection_End:    Exit FunctionCancelConnection_Err:    CancelConnection = Err    MsgBox Error$    Resume CancelConnection_EndEnd FunctionPrivate Sub Form_Load()    'KPD-Team 1999    'URL: [url][/url]    'E-Mail: [email][/email]     'to add a connection call by:    variable = AddConnection(, , )    'To cancel a connection type:    varible = CancelConnection()End Sub

Hai i would like to ask how do i used this code?? I've got it from

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?


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 ?

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")
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

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

Private Sub Form_Load()
End Sub

Private Sub Form_Resize()
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

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>

...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?

Basically I'd like to write certain math formats like fractions, division signs, exponentials ect. Please help

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

im using this DAO Code


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
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:
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

Advanced thanks

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?


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
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
        If statusOne = True Then
        Me.Label12.Caption = "Overall Result = Passed"
        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.


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.



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

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.



Replace Function, ASCII Code

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 :


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


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 i's not problem but is there an equivalent of webBrowser1.ObjectForScripting in vb6?

Please Help With My Post Code Validation Function
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



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

' 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:

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

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
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:


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!


Copyright 2005-08, All rights reserved