AddIcon

PUSH BUTTON رنگ و متن

بیمه عمر

SELECTOBJECT در WINAPI

کمبو باکس وابسته در اکسل

Named Range / Indirect

Gantt Chart

(AND(date>=start,date<=end=

(AND(D$4>=$B5,D$4<=$C5=

جستجو در لیست باکس ( Developer/ActiveX Object / Control )

روش دوم : 

استفاده از تابع SEARCH  اگرنبود تابع FIND

استفاده از تابع IF و  COUNTIF در ستون G به صورتیکه اگر سلی در ستون F برابر یک شده کانت 1 را در رنج $F$3:F3 در سل مربوطه درج کند.

 نمونه ی استفاده از تابع SEARCH در تصویر زیر 

فرمول همراه با تابع Index در ستون H 

([MATCH(lookup_value, lookup_array, [match_type

(MATCH(ROWS($G$3:G3),$G$3:$G$22,0

عملکرد تابع ROWS (برگشت  تعداد ردیف ها )  در رنج مشخص شده - وقتی فرمول به پائین کشیده شود ( DRAG )  قسمت اول که $ دارد ثابت میماند و قسمت دوم یعنی G3 اعدادش نسبت به قبلش یک واحد بصورت افزایشی تغییر میکند یعنی G3 میشود G4 و سل بعد G5 و ... 

ROWS($G$3:G3) : 1
ROWS($G$3:G4) : 2
ROWS($G$3:G5) : 3
ROWS($G$3:G6) : 4
ROWS($G$3:G7) : 5
ROWS($G$3:G8) : 6
ROWS($G$3:G9) : 7

عملکرد تابع MATCH یعنی تعیین موقعیت ( اعداد برگشتی تابع بالا پارامتر آرگومان اول تابع MATCH شده اند )

MATCH(1,$G$3:$G$22,0)=1=
MATCH(2,$G$3:$G$22,0)=7=

همانطور که در بالا مشاهده می کنید موقعیت عدد یک را در ستون G نمایش میدهد که 1 است و همینطور موقعیت عدد 2 در ستون G که 7 است را برمی گرداند و برای اعداد بعدی ( حاصل تابع ROWS بالاتر ) که 3 و ... هست چون وجودندارند صفر است .

حال تابع بالا ( MATCH ) میشود آرگومان Row در تابع INDEX 

([INDEX(array, row_num, [column_num

  (INDEX($E$2:$E$20,1=
(INDEX($E$2:$E$20,7=

:Here are the steps to create it

1. Go to Formulas –> Name Manager.
2. In the name-manager dialogue box click New. It will open a New Name dialogue box.
3. In the Name Field enter DropDownList
4. In the Refers to Field enter the formula: 
5.  =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),
6. 
   

درستون H از دوتابع MATCH و INDEX استفاده شده که تابع MATCH موقعیت موردجستجو را برمی گرداند .

Range("E3:E27").AdvancedFilter Action:=xlFilterCopy

CopyToRange:=Range("I3"), Unique:=True,

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

کمبوباکس های وابسته با Named Range

کمبوباکس های وابسته در اکسل ( Advanced Filter Vba )

Row/Rows/Min/Indirect/RandBetween

برگشت تعداد ردیف ها در یک رفرنس 

برگشت شماره ردیف  یک رفرنس 

برگشت کوچکترین مقدار در یک دسته 

برگشت رفرنس مشخص شده به حالت رشته متنی 

The Excel INDIRECT function returns a valid reference from a given text string

در اکسل اگر بخواهید در فرمول خاصی ، رنجی را بگنجانید از Indirect استفاد می کنید چون در حالت عادی نمی توانید در سل خاصی رفرنسی را بنویسید و در فرمول دیگر به آن ارجاع بدهید .

لیست کشویی در اکسل

خطای داده تکراری ( Data Validation Custom Formula )

2>(CountIf(C$2:C$7,C3=

Unique Value ComboBox

        A1     A                           B

تجربی             1001          

ریاضی            1002          

انسانی            1003          

تجربی             1001          

تجربی             1004         

In Sheet 1 

Create Named Name Range

Name Range : StuId

,OFFSET(Sheet1!$A$2, 0, 0=

 ((COUNTA(Sheet1!$A$2:$A$1000

Name Range : Course

,OFFSET(Sheet1!$B$2, 0, 0=

(( COUNTA(Sheet1!$B$2:$B$1000

 Sheet 2

 :Create Unique Value In Sheet2 

In Cell A2 

,IFERROR(INDEX(StuId, MATCH(0=

("",(COUNTIF($A$1:A,StuId), 0

Press Ctrl + Shift + Enter

In Cell B2

,IFERROR(INDEX(Course,MATCH(0=

+( COUNTIF($B$1:B1,Course)

("",0(StuId<>INDEX(Sheet2!$A$2:$A$1000,Sheet3!$B$1)

 

Press Ctrl + Shift + Enter


Create Named Range : UniqueStuId


,OFFSET(Sheet2!$A$2,  0,  0=

 (COUNT(IF(Sheet2!$A$2:$A$1000="", "",  1)), 1


Create Named Range : UniqueCourse


,OFFSET(Sheet2!$B$2,  0,  0,=

( COUNT(IF(Sheet2!$B$2:$B$1000="",  "",  1)),  1


 : In Sheet3 

( Create Combo box 1  ( Form Controls 
Input Range : UniqueStuId
( Cell Link : B2  ( Sheet 3  

 ( Create Combo Box2  ( Form Controls 
Input Range : UniqueCourse
Cell Link : C2

Array Formula

جمع تعداد کاراکترهای رنج مشخص شده را برمیگرداند .

=SUM({3;3;5;4;5;4;6;5;4;4})

=SUMPRODUCT(LEN(A1:A5))

تابع Unique

Note: The UNIQUE function is a new "Dynamic Array Function" in Excel. It is a beta feature

where "list" is the named range B5:B11.

Note: this is an array formula and must be entered using control + shift + enter.

([MATCH(lookup_value, lookup_array, [match_type

MATCH(0,{0;0;0;0;0;0;0},0) // 1 (Joe)
MATCH(0,{1;0;0;0;1;0;0},0) // 2 (Bob)
MATCH(0,{1;1;0;0;1;0;1},0) // 3 (Sue)
MATCH(0,{1;1;1;1;1;0;1},0) // 6 (Aya)

Last Row / Delete Row در اکسل

range.end

range.specialcells

range.entirerow

range.activate

range.select

range.delete

range.copy      ' Copy To ClipBoard

range.pastespecial

  range.interior   ' Color

range.borders

UniqueValue

Arr Can Be Array Or Rang

 خروجی میتواند آرایه ای از ولیوهای بدون تکرار باشد 

Dim E As Variant

Dim Unique() As Variant

Dim NotMatch As Boolean 

Dim Num As Long

Num=0

(  For Each E In Arr  '  ( Array Or Range

NotMatch=True

For i=1 To Num                   

if E=U ique(i) Then                            

NotMatch=False                           

 goto NextPos                              

End If                           

Next                    

  NextPos  :  ' AddItem

if NotMatch Then 

Num=Num+1

Redim'

Unique(i)=E

End if

Next

UserControl در اکسل ... کد نویسی

منبع زیر از سایت خارجی است  و طبق نگاه اجمالی و کدنویسی VBA باید درست باشد.

درتابع زیر چنانچه در اولین کنترل که editstudent1 است اینتر زده شود تابع Findit فایرشده و مقدارآن در متغیر تعریف شده ی  Search  را در رنج A پیدا میکند ( متد Find ) ، اگر پیداشد با لوپ زدن در کنترل ها که شروع آن  از 2  است  و به 13 ختم میشود ( منظور نام  کنترلها در UserForm از editstudent1 شروع شده تا editstudent13 )  مقدار کالمن ها در ردیف پیداشده در شیت را ( با متد Cells ) به آن کنترل ها انتقال میدهد.

Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 

    If KeyCode=vbKeyReturn Then 

        Findit 

    End If 

End Sub

Private Sub Findit() 'Find and populate the records with Excel VBA

Dim fnd As Range

Dim Search As String

Dim sh As Worksheet

Dim i As Integer

  Set sh=Sheet2

Search=editstudent1.Text

(Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole

    If fnd Is Nothing Then

        MsgBox "No Person Found", , "Error"

        editstudent1.Text=""

        frmeditrecord.Hide

    Else

        For i=2 To 13 'There are 13 items in the userform.

            frmeditrecord.Controls("editstudent" & i).Text=sh.Cells(fnd.Row, i).Value

        Next i

    End If

End Sub

Private Sub cmdUpdate_Click() 'Push data back to the tab with Excel VBA
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Dim ctl As Object Set sh=Sheet2
Search=editstudent1.Text

(Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole
      For i=2 To 13
         sh.Cells(fnd.Row, i).Value=frmeditrecord.Controls("editstudent" & i).Text
    Next i

حذف کردن محتویات ادیت باکس کنترل های تیپ تکست باکس در UserForm.

    'Clear Form controls
    For Each ctl In Me.Controls
       If TypeName(ctl)="TextBox" Then ctl.Value=Null
    Next ctl
End Sub

(Range.Find method (Excel

مثال زیر که از متد بالا نشات گرفته محتویات رنج تعریف شده اگر 2 بود را به 5 تغییر میدهد.

This example selects cell A154 on Sheet1 and then scrolls through the worksheet to display the range

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"),     scroll:=True 

Jump

("Set ws2=Sheets("Sheet2

ws2.Range("A" & RowNum).Select

نمایش BITMAP نزدیک به منوآیتم ( سایت آفیس NEXT MENU ITEM )

 این ویژگی ( Feature ) تست نشده و فقط طبق سند آفیس و جذاب بودنش در این صفحه درج شده .

To add a new bitmap item to a menu, use the MF_BITMAP flag with the InsertMenu or AppendMenu

 function.

MF_BITMAP=&H4 

'Uses a bitmap as the menu item. The lpNewItem parameter contains a handle to the bitmap.

InsertMenuA hMenu,uPosition:6,uFlags:MF_BYPOSITION+MF_BITMAP, 

uIDNewItem:6,lpNewItem:Handle To Bitmap

DrawMenuBar hSysMenu/hMenu/hSubMenu

setmenuitembitmaps hSysMenu, 5, &H400, loadimage(image_Bitamp), loadimage(image_Bitamp

CHOOSE - OFFSET - INDEX

(CHOOSE(position in list,item1,item 2, etc=

تابع Choose طبق عدد 3 ( موقعیت در لیست )  آیتم 3 را در نظر میگیرددکه C1:C3 است و تابع SUM هم این رنج را جمع میزند.

((SUM(CHOOSE(3,A1:A3,B1:B3,C1:C3,D1:D3=

 returns the sum of cells C1 to C3

محتویات C1 را برمیگرداند که آرگومان دوم ردیف ( 0 )  به سمت پائین و آرگومان سوم ستون ( 2  )  به سمت راست است . در اینج پارامتر آرگومان Rows صفر است پس در همان ردیف میماند و با پارامتر آرگومان Cols که 2 است  از سل A1 دو سل غیر از خودش به سمت راست میرود و محتویات سل C1 را نمایش میدهد درصورتیکه این نمایش چندین سل باشد VALUE# را بر میگرداند این تابع درون تابع های دیگر نظیر SUM استفاده میشود. 

(OFFSET(A1,0,2 =

returns the contents of C1  * A1-> B1->C1

در مثال زیر تابع OFFSET ... از سل A1 آغاز میشود در همان سل میماند و بغیر از ستونی که قرار دارد 2 ستون به سمت راست میرود که میشود C1 و نهایتا به 3 سل پایینتر ختم میشود یعنی C3 پس رنج میشود C1:C3   و نهایتا در تابع SUM محاسبه میشود البته اگر محتویات عدد باشد.

SUM(OFFSET(A1,0,2,3)) will return the sum of the=

range C1 to C3, being offset by zero rows and two columns and having a height of 3

تابع زیر در رفرنس یا آرایه ی مشخص شده محتویات سل را برمی گرداند با توجه به آرگومانهای دوم ( شماره ردیف در همان محدوده ) و سوم  ( شماره ستون در محدوده ی تعیین شده در آرگومان اول ) .

شماره ردیف اجباری و ستون انتخابیست اگر شماره ردیف درنظرگرفته شود ذکر شماره ستون الزامیست 

INDEX(A1:C1,3) will return the contents of C1=

If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array

اگر آرایه بیشتر از یک ردیف یا بیشتر از یک ستون داشته باشد و فقط شماره ی ردیف یا فقط شماره ی ستون استفاده شده باشد این تابع تمام ردیف یا تمام ستون را در آرایه برمی گرداند . مثل مثال زیر INDEX Sales,,1 که شماره ردیف در آن استفاده نشده در نتیجه کل ستون یک را بر میگرداند یعنی G22:G25 

چارت پویا و استفاده از چک باکس ( مقایسه ی مالی با سال های قبل )

کپی کردن دیتای جدول بالا به پایین و نامگذاری چک باکس ها به 2011 و 2012 و لینک به سل هایی که بین دوتا جدول است و True نوشته شده در جدول پایین بجای دیتاهای 2011 و 2012 باید نوشته شود اگر C7=True,C3,False و برای 2012 هم همینطور و چارت کشیده شود که باید دیتا سورس هاعوض شود.

تابع CHOOSE در اکسل ( انتخاب از چندین انتخاب در آرگومانهای ولیو )

یک تا 254 ولیو ( منظورتعداد آرگومانهای ولیو)

80=23+45+12

در تصویر پایین براساس آرگومان اول تابع  که  Index_Num است ( طبق سل B1 ) جمع سومین ولیو با تابع SUM  که A1:A3 است ( 10+11+12=33 )برگشت داده میشود.

آیدی کنترل ها در MessageBox

https://www.cadsharp.com/docs/Win32API_PtrSafe.txt

IDABORT 3	The Abort button was selected.
IDCANCEL 2	The Cancel button was selected.
IDCONTINUE 11	The Continue button was selected.
IDIGNORE 5	The Ignore button was selected.
IDNO 7	The No button was selected.
IDOK 1	The OK button was selected.
IDRETRY 4	The Retry button was selected.
IDTRYAGAIN 10	The Try Again button was selected.
IDYES 6	The Yes button was selected.

SendDlgItemMessageA

SetDlgItemTextA

تابع OFFSET (احصاء رفرنس جدید باتوجه به جابجایی آن توسط آرگومانها )

مرجع یا رفرنسی به محدوده ای که تعداد مشخصی از ردیف ها و ستون ها از یک سلول یا دامنه سلول است را برمی گرداند. مرجع برگشتی می تواند یک سلول واحد یا طیف وسیعی از سلول باشد. می توانید تعداد ردیف ها و تعداد ستون هایی که باید برگردانید را مشخص کنید.

([OFFSET(reference, rows, cols, [height], [width

آرگومان دوم و سوم که اجباریست میتواند هم عدد مثبت باشد و هم منفی ولی دو تای آخر انتخابیست یا به قولی آپشنال حتما باید عدد مثبت باشد در غیر اینصورت با REF# مواجه خواهید شد... فرضا اگر Cols عدد ۵ در نظر گرفته شود جابجایی از رفرنس تعریف شده 5 کالمن به سمت راست می باشد( بغیر از خود ردیفی  که قرار است در نظر گرفته شود برای حرکت کردن افقی ) یا اگر منفی باشد 5 کالمن به سمت چپ .اگر Cols یا Rows هم وجود نداشته باشد منظور تعداد ردیف ها بیشتر از آنچه باشد که در نسخ قدیم و جدید است خطای REF# دوراز انتظار نیست یا Refrens فرضا D2 باشد و شما در Cols عدد 4- را ذکر کنید.البته امتحان کنید تا صحت یا عدم آن اثبات شود.

در کل منظور این است که سلی را جابجا نمیکند فقط مرجعی میدهد که در توابع دیگر استفاده شود.

SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2  columns to the right of cell C2

SUM(B10:C10)

چارت پویا در اکسل

در تصویر زیر در سل B12 از Data-Validation برای  ماه ها استفاده شده .

سل Q4 را برابر سل B12 یعنی لیست کشوئی اعمال شده توسط پنجره ی Data Valudation

Cell Q4 = B12

CELL Q5=INDEX($C$4:$N$9,MATCH(P5,

( $B$5:$B$9,0),MATCH($Q$4,$C$4:$N$9,0,